user2904400 user2904400 - 3 months ago 9
SQL Question

T-SQL Loop through dates

I have a list of dates (more than 100) where i need to sum a particular column for each of these dates. I know the below doesn't work but it shows what i'm trying to do...please help

USE FIND
select SUM(a.rm_result_as_pct_of_nav)
from rpt.rm_Report_History a
where
a.analysis_date IN( '20160330','20160228','20160131')
and a.criteria_Set = 'Daily' and a.ptf_id = '10039'
and a.stat_def_id=397

Answer

This will give you the sum and show the date;

SELECT
a.analysis_date
,SUM(a.rm_result_as_pct_of_nav) SumResult
FROM rpt.rm_Report_History a
WHERE a.analysis_date IN ('20160330','20160228','20160131')
    AND a.criteria_Set = 'Daily' 
    AND a.ptf_id = '10039'
    AND a.stat_def_id=397
GROUP BY a.analysis_date

If you don't want to restrict the dates then do something like this;

SELECT
a.analysis_date
,SUM(a.rm_result_as_pct_of_nav) SumResult
FROM rpt.rm_Report_History a
WHERE a.criteria_Set = 'Daily' 
    AND a.ptf_id = '10039'
    AND a.stat_def_id=397
GROUP BY a.analysis_date