Vamshi Avadhanula
asked on
Please help me with the query
Can you please help me with the below query:
SELECT
SM_SEC_GROUP,
SM_SEC_TYPE,
MAX (ABS(MKT_VALUE * r.rate)) AS 'MKV_USD',
MAX (ABS(MKT_NOTION * r.rate)) AS 'NOTIONAL_USD',
COUNT (*) AS 'NUM_OF_HOLDINGS' ,
MAX(FI.MATURITY) AS MATURITY
-- INTO #FI_Summary
FROM
iim_risk_point.dbo.FI_PORT
dbo.Account ACT,
dbo.fx_rate r
WHERE
ASOF_DATE = '12/29/2017' AND
FI.MATURITY < dateadd(dd, 30, '12/29/2017') AND
FI.PORTF_LIST = ACT.ID_ALADDIN AND
ACT.STATUS = 'A' AND
ACT.NME_GC_LVL1 IN('Fixed Income','Liquidity' ) AND
MKT_VALUE <> 0 AND
FI.PORT_CURRENCY = r.curr_sold AND
r.curr_bought = 'USD' AND
FI.ASOF_DATE = r.date
GROUP BY
SM_SEC_GROUP,
SM_SEC_TYPE
ORDER BY 1,2
the dateadd function is not giving correct results. what am I doing wrong.
SELECT
SM_SEC_GROUP,
SM_SEC_TYPE,
MAX (ABS(MKT_VALUE * r.rate)) AS 'MKV_USD',
MAX (ABS(MKT_NOTION * r.rate)) AS 'NOTIONAL_USD',
COUNT (*) AS 'NUM_OF_HOLDINGS' ,
MAX(FI.MATURITY) AS MATURITY
-- INTO #FI_Summary
FROM
iim_risk_point.dbo.FI_PORT
dbo.Account ACT,
dbo.fx_rate r
WHERE
ASOF_DATE = '12/29/2017' AND
FI.MATURITY < dateadd(dd, 30, '12/29/2017') AND
FI.PORTF_LIST = ACT.ID_ALADDIN AND
ACT.STATUS = 'A' AND
ACT.NME_GC_LVL1 IN('Fixed Income','Liquidity' ) AND
MKT_VALUE <> 0 AND
FI.PORT_CURRENCY = r.curr_sold AND
r.curr_bought = 'USD' AND
FI.ASOF_DATE = r.date
GROUP BY
SM_SEC_GROUP,
SM_SEC_TYPE
ORDER BY 1,2
the dateadd function is not giving correct results. what am I doing wrong.
ASKER
I tried with the day and month, but it is not giving desired results. I am getting records with in a month. (I should be be getting records after a month). please help me .
ASKER
This issue little bit urgent for me . Can someone please help me asap.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Try : dateadd(day, 30, '12/29/2017') or dateadd(month, 1, '12/29/2017') if you try to add a month