T-SQL Loop through dates

Question

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

There are no errors but I just get 1 total. I need the total per date, so i can see at the end of each month the pct_of_nav is x.

No errors but i just get 1 total...i need the total per date, so i can see at the end of each month the pct_of_nav is x — Mark Banford
– Mark Banford, Commented Aug 23, 2016 at 13:00

Rich Benner · Accepted Answer · 2016-08-23 12:57:51Z

2

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

answered Aug 23, 2016 at 12:57

Rich Benner

8,1419 gold badges35 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mark Banford Over a year ago

again only gives 30th June results, not the others

Rich Benner Over a year ago

Ok, could you update your question and include your sample data? I'm assuming that one of the fields in the where clause is restricting the data. Try it without the where clause to check that it's working. Then add in the clauses and see which one restricts the data to only the 30th of June.

Mark Banford Over a year ago

yes my bad, the dates i specified were not avaialbe,,,you answered it thanks

Rich Benner Over a year ago

Happy to help. Feel free to upvote and mark as accepted if this answer has resolved the issue.

Collectives™ on Stack Overflow

T-SQL Loop through dates

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related