CODEWITHSUNDEEP
sql-server
diehard
diehard

Reputation: 11

sum values for max month/year in SQL

I have a table (t_psg2_nav) with relevant columns: fundnum, calcdate, return_active_daily which has a time series of active returns for a set of funds. i want to sum return_active_daily for the most recent month. i have the below but it returns nothing. not sure if i'm on the right track or not, how do i accomplish this? specific coding would be helpful as i'm very new to this:

SELECT      nav.fundnum AS 'Fund'
            ,sum(nav.return_active_daily) AS 'MTD Tracking'

FROM       t_psg2_nav nav

INNER JOIN  (SELECT fundnum
                    ,month(max(calcdate)) AS calcdate
                    ,return_active_daily
             FROM   t_psg2_nav
             GROUP BY fundnum, return_active_daily
             )
AS nav1 ON nav.fundnum = nav1.fundnum
AND nav.calcdate = nav1.calcdate

GROUP BY nav.fundnum

Upvotes: 0

Views: 339

Answers (1)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

Your error is in the subquery; MONTH will return an integer representing the month (ie., 1-12). When you join that using the syntax (nav.calcdate = nav1.calcdate), there's no date that equals "10".

Try this instead:

SELECT  nav.fundnum AS 'Fund'
      , SUM(nav.return_active_daily) AS 'MTD Tracking'
FROM    t_psg2_nav nav
WHERE nav.calcdate >= DATEADD(MONTH, DATEDIFF(MONTH,0, SYSDATETIME()), 0)        
GROUP BY nav.fundnum

Assuming that you alays want the rate of return for the current month.

Upvotes: 1

Related Questions