How to loop through date variable (run report for multiple dates)
I have a sql stored procedure with 2 date variables that I need to run the report on. @StartDate and @EndDate. The Stored procedure runs a census report for the dates provided. I need to run the SP for current date and the past 15 days. The current date is easy as I can set @StartDate = getdate() but how can I also run it for the past 15 days including todays date. Also in order for the data to be correct the @StartDate and @EndDate have to be the same date so if I am running the report for today @StartDate = 1/24/2017 and @EndDate = @StartDate and yesterdays date would be @StartDate=1/23/2017 and @EndDate=1/23/2017. (It can't be a date range @StartDate = 1/09/2017 @EndDate = 1/24/2017) one way i know is running the SP 15 different times like below but there has to be a better way. exec sp_report '1/24/2017','1/24/2017' exec sp_report '1/23/3017'.'1/23/2017' etc... declare @StartDate smalldatetime declare @EndDate smalldatetime set @StartDate = getdate() set @EndDate = @StartDate is loop the way to go or is there a better approach? All the help is greatly appreciated.
For 15 rows of SQL writing a loop probably isn't worth it, personally I would just have create procedure sp_reportBatch as being declare @StartDate smalldatetime = getdate(); declare @EndDate smalldatetime = @StartDate; execute sp_report @StartDate, @EndDate execute sp_report dateadd(dd,-1,@StartDate), dateadd(dd,-1,@EndDate) execute sp_report dateadd(dd,-2,@StartDate), dateadd(dd,-2,@EndDate) execute sp_report dateadd(dd,-3,@StartDate), dateadd(dd,-3,@EndDate) etc...... end Fill in the extra rows until dateadd -15 and then you just execute sp_reportbatch