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

    Join Date
    Oct 2012
    Posts
    20
    Rep Power
    0

    Question 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. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    854
    Rep Power
    387

    Cool


    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"?
  4. #3
  5. 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 !
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    854
    Rep Power
    387

    Thumbs down


    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?
  8. #5
  9. 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)

IMN logo majestic logo threadwatch logo seochat tools logo