How to create a date loop in SQL

Question

This is my statement below:

SELECT [Opp ID], max([Record ID]) as [Record ID]
FROM Opp History Table
WHERE [Last Modified Date] < '2015-01-06' 
GROUP BY [Opp ID] 

I am trying to construct a table that retrieves data that shows how the forecast is changing every week.

I need the most recent opp records for every week starting at '2015-01-05'. How would I write a script that performs the above statement but for increments of 7 days ( [Last Modified Date] + 7 days ).

Also, is there anyway I can create a field such as "Snapshot Date" that would tag every opp with that date of interest?

Thank you. Any help is appreciated!

Answer 1

I would consider creating a date dimension table like this which would let you do joins on your dates to get additional date-related info - if needed.

Otherwise, just include the DatePart for the week of your date column, and include it in your GROUP BY. Either way, you don't need a loop.

SELECT [Opp ID], max([Record ID]) as [Record ID], DatePart(WEEK, [Last Modified Date])
FROM Opp History Table
WHERE [Last Modified Date] < '2015-01-06' 
GROUP BY [Opp ID], DatePart(WEEK, [Last Modified Date])