This snippet is so nice, I’m posting it again!

I’ve utilized the script below at least once a week, for either reports, or working with large data sets. Rather than reinventing the wheel, I’m posting my code here and to GitHub for everyone to see.

The script expects two date parameters, a start date and an overall end date. The script will then assign a third variable, end date, seven days after the start date. The script will execute your given code. The final step inside the loop increments the start date and end date by seven days. The script will loop until the start date is greater then the overall end date.

declare @StartDate      date
declare @EndDate        date
declare @OverallEndDate	date
declare @PrintStart     varchar(10)
declare @PrintEnd       varchar(10)

--	Set the start date and overall end date
--	Set the end date to 7 days after the start date
set @OverallEndDate = '2018-12-31'
set @StartDate = '2018-01-01'
set @EndDate = dateadd( dd,7 ,@startDate)

--	Loop while the start date is less than the overall end date
while(@OverallEndDate > @StartDate)
begin
        --     Print the working weeks
        set @PrintStart = CONVERT( varchar(10),@StartDate, 120)
        set @PrintEnd = CONVERT( varchar(10),@EndDate, 120)
        raiserror('Working on %s to %s' ,10, 1,@PrintStart ,@PrintEnd)

        --     Do something here
		--	select  [SalesOrderID]
		--	from	[Sales].[SalesOrderHeader]
		--	where	[OrderDate] >= @StartDate
		--	and		[OrderDate] < @EndDate

        --     Increment the weeks
        set @StartDate = @EndDate
        set @EndDate = dateadd( dd,7 ,@startDate)

end
raiserror('Finished!' ,10, 1)

As you can see, the loop iterates over all dates, and completes successfully.

If you’re working with a significantly large data set, you can modify the code to increment by one day instead of seven.

Follow this project on GitHub:

Rudy

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter