-1

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!

Alicia
  • 1,152
  • 1
  • 23
  • 41
J.Sun
  • 13
  • 2

1 Answers1

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])
Community
  • 1
  • 1
InbetweenWeekends
  • 1,405
  • 3
  • 23
  • 28
  • I join this table ON Record ID with the rest of the Opp History Table to retrieve the opp related info. However my current query only retrieves the most recent modified opps for the "snapshot date" of 1/5/2015. I believe I still need a loop to retrieve the most recent modified opps for 1/12/15, 1/19/15, etc... – J.Sun Dec 01 '15 at 16:41