Simply SQL Loops

Tuesday, May 27, 2014 1:37 PM by nairdo

(I'm mostly writing this for myself... to remind myself how easy this can be.)

I recently needed to record some metrics for our prayer ministry using data we've been collecting over the past 5 years.  We had the data in various other tables, so I just needed a quick way to pull them out and insert them into our metric table.

No complex CTEs, recursion, cursors, etc. are necessary when you just need to crank through N sets of date ranges.

Here's an example using monthly date ranges.  All that's needed is a start date, an end date, and the query you need to run for each month (for something BETWEEN 1st of previous month and 1st of current month):

-- Set your start date and end date
Declare @StartDt DATE = '05/01/2009'
Declare @EndDt DATE = GetDate()
DECLARE @DateA DATE
DECLARE @DateB DATE
WHILE @StartDt < @EndDt
	BEGIN
		-- First day of previous month
		--SELECT DATEADD(month, DATEDIFF(MONTH, 0, @StartDt) - 1, 0)
		
		-- First day of current month
		--SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDt), 0)
		
		-- First day of next month
		--SELECT DATEADD(month, DATEDIFF(MONTH, 0, @StartDt) + 1, 0)
		
		-- Our range will be 1st of previous month and 1st of current month.
		SET @DateA = (SELECT DATEADD(month, DATEDIFF(MONTH, 0, @StartDt) - 1, 0))
		SET @DateB = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDt), 0))
		
		-- Your query goes here... take this abbreviated example
		--INSERT INTO mtrc_metric_item
		--( 
		--	[metric_id], 
		--	[metric_value], 
		--	[collection_date])
		--SELECT
		--	65,
		--	COUNT(1),
		--	@DateA
		-- FROM pryr_request WHERE date_created BETWEEN @DateA AND @DateB
	-- Increment to the next month
	SET @StartDt = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDt) + 1, 0))
	
	END

For yearly ranges just make the necessary adjustments.   

 

Comments

    No Comments

New Comments to this post are disabled

Powered by Community Server, by Telligent Systems