Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Find the last 6 Tuesdays or Wed or Whatever day. Expand / Collapse
Author
Message
Posted Friday, August 02, 2013 6:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 06, 2013 12:19 PM
Points: 8, Visits: 7
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
Post #1480354
Posted Friday, August 02, 2013 8:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 07, 2014 7:35 AM
Points: 1,172, Visits: 2,413
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


Glad it worked for you, and thanks for letting us know it did!


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1480428
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse