### Thread: To find the result like (query1/query2/365) for 100's of dates

1. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Oct 2012
Posts
20
Rep Power
0

#### To find the result like (query1/query2/365) for 100's of dates

Hello Everyone ,

I have small Doubt in the below query .Before we get into the query here is the logic

------------------------------------------------
Result on Query 1:

SUM(****) || PGMCODE
18256896 FIXED DEP
18256896 DUTCH DEP

Result on Query 2:

SUM(****) || PGMCODE
450235622 FIXED DEP
256522558 DUTCH DEP

For the Date 1 JAN 2013

LOGIC

18256896 / 450235622 / 365 ==> round(result from the left,2)
---------------------------------------

With above given logic , i have to calculate it for 100's of date[s] .. I want to dynamically pass my the each of the dates get calculated with the above logic and need to return the result for each date ! Is't possible to write in a query ?

QUERY 1 --> Select Statment
-------------------------------------------
select

sum((Query1.DAYS_REMAIN)*(Query1.AMT)),
QUERY1.PGM_CODE

FROM
(select distinct
TRD.TRD_ID,
SUM(LEG.LCL_NMNL_AMT) nomin_amt,
EP.EXTL_PARTY_CODE,
ME.GECS_IND,
TRD.PGM_CODE PGM_CODE,
TRD.TRD_MATY_DATE TRD_MATY_DATE,
TRD.TRD_SETL_DATE,
(TRD.TRD_MATY_DATE-DT.CAL_DATE) DAYS_REMAIN,
SUM((LEG.LCL_NMNL_AMT) * (TRD.TRD_MATY_DATE-DT.CAL_DATE)),
SUM (LEG.YLD_RATE * (LEG.LCL_NMNL_AMT)),
SUM((LEG.LCL_NMNL_AMT*DR.DAILY_CURR_CNV_RATE)) as ""AMT""
--sum (SUM(LEG.LCL_NMNL_AMT) over (partition by TRD.PGM_CODE))
--sum((LEG.LCL_NMNL_AMT) over (partition by TRD.PGM_CODE))

from
EDW.T_DIM_TRD TRD,
EDW.T_DIM_CURR CUR,
EDW.T_DIM_MNGMT_ENT ME,
EDW.T_FACT_TRD_LEG LEG,
EDW.T_DIM_EXTL_PARTY EP,
EDW.T_DIM_DATE DT,
EDW.T_FACT_DAILY_RATE DR

where

TRD.TRD_ID = LEG.TRD_ID
and LEG.LC_KEY = CUR.CURR_KEY
and EP.EXTL_PARTY_KEY= LEG.EXTL_PARTY_KEY
and LEG.MNGMT_ENT_KEY = ME.MNGMT_ENT_KEY
and TRD.SRCE_SYS_CODE = 'ATOM-SD'

and LEG.ACTV_FLAG = 'Y'
and TRD.ACTV_FLAG ='Y'
and TRD.TRD_STATUS_CODE <>'CA'
and DR.DATE_KEY = DT.DATE_KEY
and DR.FROM_CURR_KEY = CUR.CURR_KEY
and DR.EXCH_RATE_TYPE_KEY = 4
and DR.TO_CURR_KEY = 368
AND TRD.PGM_CODE IN ('GECD','GECDFSB')
and DT.CAL_DATE ='31-JAN-2012'
--and DT.CAL_DATE <='31-JAN-2012'

and TRD_MATY_DATE > '31-JAN-2012'
AND trd_setl_date <= '31-JAN-2012'

group by TRD.TRD_ID, EP.EXTL_PARTY_CODE, ME.GECS_IND, TRD.PGM_CODE, TRD.TRD_MATY_DATE, TRD.TRD_SETL_DATE, (TRD.TRD_MATY_DATE-DT.CAL_DATE), (LEG.LCL_NMNL_AMT*DR.DAILY_CURR_CNV_RATE)

)QUERY1

group by QUERY1.PGM_CODE

QUERY 2 --> Select Statment
-----------------------------------------

"select

sum((Query1.AMT)) ,
QUERY1.PGM_CODE

FROM
(select distinct
TRD.TRD_ID,
SUM(LEG.LCL_NMNL_AMT) nomin_amt,
EP.EXTL_PARTY_CODE,
ME.GECS_IND,
TRD.PGM_CODE PGM_CODE,
TRD.TRD_MATY_DATE TRD_MATY_DATE,
TRD.TRD_SETL_DATE,
(TRD.TRD_MATY_DATE-DT.CAL_DATE) DAYS_REMAIN,
SUM((LEG.LCL_NMNL_AMT) * (TRD.TRD_MATY_DATE-DT.CAL_DATE)),
SUM (LEG.YLD_RATE * (LEG.LCL_NMNL_AMT)),
SUM((LEG.LCL_NMNL_AMT*DR.DAILY_CURR_CNV_RATE)) as ""AMT""
--sum (SUM(LEG.LCL_NMNL_AMT) over (partition by TRD.PGM_CODE))
--sum((LEG.LCL_NMNL_AMT) over (partition by TRD.PGM_CODE))

from
EDW.T_DIM_TRD TRD,
EDW.T_DIM_CURR CUR,
EDW.T_DIM_MNGMT_ENT ME,
EDW.T_FACT_TRD_LEG LEG,
EDW.T_DIM_EXTL_PARTY EP,
EDW.T_DIM_DATE DT,
EDW.T_FACT_DAILY_RATE DR

where

TRD.TRD_ID = LEG.TRD_ID
and LEG.LC_KEY = CUR.CURR_KEY
and EP.EXTL_PARTY_KEY= LEG.EXTL_PARTY_KEY
and LEG.MNGMT_ENT_KEY = ME.MNGMT_ENT_KEY
and TRD.SRCE_SYS_CODE = 'ATOM-SD'

and LEG.ACTV_FLAG = 'Y'
and TRD.ACTV_FLAG ='Y'
and TRD.TRD_STATUS_CODE <>'CA'
and DR.DATE_KEY = DT.DATE_KEY
and DR.FROM_CURR_KEY = CUR.CURR_KEY
and DR.EXCH_RATE_TYPE_KEY = 4
and DR.TO_CURR_KEY = 368
AND TRD.PGM_CODE IN ('GECD','GECDFSB')
and DT.CAL_DATE ='31-JAN-2012'
--and DT.CAL_DATE <='31-JAN-2012'

and TRD_MATY_DATE > '31-JAN-2012'
AND trd_setl_date <= '31-JAN-2012'

group by TRD.TRD_ID, EP.EXTL_PARTY_CODE, ME.GECS_IND, TRD.PGM_CODE, TRD.TRD_MATY_DATE, TRD.TRD_SETL_DATE, (TRD.TRD_MATY_DATE-DT.CAL_DATE), (LEG.LCL_NMNL_AMT*DR.DAILY_CURR_CNV_RATE)

)QUERY1

group by QUERY1.PGM_CODE

2. Originally Posted by srinidelite
Hello Everyone ,

I have small Doubt ...
... , i have to calculate it for 100's of date[s] .. I want to dynamically pass my the each of the dates get calculated with the above logic and need to return the result for each date ! Is't possible to write in a query ?
... E t c ...
Where are these dates? In a table? spreadsheet?
Can they be derived from the source table(s)? Like "monthly/weekly/daily"?
3. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Oct 2012
Posts
20
Rep Power
0
Originally Posted by LKBrwn_DBA
Where are these dates? In a table? spreadsheet?
Can they be derived from the source table(s)? Like "monthly/weekly/daily"?
I may want to give it like Var_name = cal_date between (start_date to End_date)

to find the result calculation for each date !

4. Originally Posted by srinidelite
I may want to give it like Var_name = cal_date between (start_date to End_date)

to find the result calculation for each date !
You want to give the dates MANUALLY?
5. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Oct 2012
Posts
20
Rep Power
0

#### Yes Manually not from the tables

Originally Posted by LKBrwn_DBA
You want to give the dates MANUALLY?
Yes .. I want to give like dates between ( 01-AUG-2013 to 12-AUG-2013)