tdanley (8/2/2013)

---

Thanks this worked great! WOLFILLJ

wolfkillj (7/30/2013)

---

tdanley (7/29/2013)

---

I have a sales report that shows the previous days sales. I need to modify to a rolling report that shows the previous 6 of that day of a week. So Monday shows the pervious 6 Monday's, Tuesday previous 6 Tuesdays. I suppose I could put it all in a static table and keep it there and then very seventh week delete the oldest week but there should but should be something easier than keep all data sitting in a table.

Any thoughts or ideas how to find the dates for the previous instances of the day of the week?

Since you will know the date of the previous day when you run the report, why not use that date and the code below to generate the dates for the preceding six instances of that day of the week? Once you have that set, you can use the values to filter the sales data that will appear in the report.

DECLARE @reportDate date = '2013-07-29'

;WITH smallTally(n) AS

(SELECT TOP(6) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) * -1

FROM sys.all_columns)

SELECT DATEADD(week, n, @reportDate) AS priorDate

FROM smallTally

Results:

priorDate

2013-07-22

2013-07-15

2013-07-08

2013-07-01

2013-06-24

2013-06-17