July 29, 2013 at 11:55 am
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?
July 29, 2013 at 12:00 pm
What about a simple DATEADD(wk, -1, GETDATE()) and change the value to bee substracted?
Or use DATEADD(wk, -6, GETDATE()) AND DATENAME(dw, date_needed) = 'Tuesday'?
It's an idea. You could also use DATEPART.
July 29, 2013 at 12:04 pm
I'm thinking something along the lines of
SELECT TOP 6 cols
FROM tables
WHERE DATENAME(weekday, dateCol) = DATENAME(weekday, GETDATE());
July 29, 2013 at 1:24 pm
bartedgerton (7/29/2013)
I'm thinking something along the lines ofSELECT TOP 6 cols
FROM tables
WHERE DATENAME(weekday, dateCol) = DATENAME(weekday, GETDATE());
If you are going to use TOP 6 here you MUST supply an order by clause or it will just whatever 6 rows sql feels like.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 29, 2013 at 3:28 pm
Using this sample data:
--Adjust for the number of days of sample data you would like
DECLARE @days int = 1000;
IF OBJECT_ID('tempdb..#sales') IS NOT NULL
DROP TABLE #sales;
CREATE TABLE #sales
(sale_id int identity primary key,
sale_date date not null,
sale_amt money not null);
WITH small_tally(n) AS
(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM sys.all_columns)
INSERT INTO #sales
SELECT CAST(DATEADD(DAY,n,(DATEADD(DAY,@days*(-1),getdate()))) AS date),
ABS(CHECKSUM(NewId()))%200
FROM small_tally
WHERE n<=@days
You could do something like this:
DECLARE @day varchar(20) = 'Tuesday',--the day
@wks_back tinyint = 6;--# of weeks back
SELECT TOP(@wks_back)
DATENAME(WEEKDAY,sale_date) AS sale_day,
sale_date,
sale_amt
FROM #sales
WHERE @day=DATENAME(WEEKDAY,sale_date)
AND sale_date<=getdate()
ORDER BY sale_date DESC
This would give you the sales for the last 6 Tuesdays. You could adjust @day and @wks_back as needed.
-- Itzik Ben-Gan 2001
July 30, 2013 at 8:50 am
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
Jason Wolfkill
July 30, 2013 at 9:13 am
Alan.B (7/29/2013)
Using this sample data:<snipped>
This would give you the sales for the last 6 Tuesdays. You could adjust @day and @wks_back as needed.
Alan.B's code only works when there is only one sale per day (as is the case in his sample data).
If you create sample data that has more than one sale per day, the TOP(@wks_back) clause will limit results to only the most recent n sales on the specified day of the week. If there were seven sales last Tuesday, you'd get six of them. You could take out the TOP clause and use this query as an intermediate step to collect all the Tuesday sales, then select the aggregate totals for only the six most recent Tuesdays, but you'd have to scan the entire #sales table because of the non-SARGeable predicate:
@day=DATENAME(WEEKDAY,sale_date)
If you used the code I posted above to find the dates of the previous six instances of the given day of the week, you could create a SARGeable predicate
sale_date IN (SELECT priorDate FROM [temp table, table variable, or CTE based on my code])
that could take advantage of an index on sale_date to perform a seek rather than a scan.
Jason Wolfkill
July 31, 2013 at 2:19 pm
SELECTDATEADD(DAY, Number, '17530101')
FROM(
VALUES(DATEDIFF(DAY, '17530108', GETDATE())),
(DATEDIFF(DAY, '17530115', GETDATE())),
(DATEDIFF(DAY, '17530122', GETDATE())),
(DATEDIFF(DAY, '17530129', GETDATE())),
(DATEDIFF(DAY, '17530205', GETDATE())),
(DATEDIFF(DAY, '17530212', GETDATE()))
) AS d(Number)
N 56°04'39.16"
E 12°55'05.25"
July 31, 2013 at 3:41 pm
You can use the general calc shown below to get the nearest day of any day:
SELECT
date,
DATEADD(DAY, DATEDIFF(DAY, '19000101', date) / 7 * 7, '19000101') AS monday_on_or_before_date,
DATEADD(DAY, DATEDIFF(DAY, '19000102', date) / 7 * 7, '19000102') AS tuesday_on_or_before_date,
DATEADD(DAY, DATEDIFF(DAY, '19000103', date) / 7 * 7, '19000103') AS wednesday_on_or_before_date
FROM (
SELECT GETDATE()-3 AS date UNION ALL
SELECT GETDATE()-2 AS date UNION ALL
SELECT GETDATE()-1 AS date UNION ALL
SELECT GETDATE() AS date UNION ALL
SELECT GETDATE()+1 AS date UNION ALL
SELECT GETDATE()+5 AS date UNION ALL
SELECT GETDATE()+11 AS date
) AS test_dates
Once that calc gets you the first date you need, you simply add -7, -14, -21, etc., days to go back as many weeks as you want.
For example, for the last 6 Mondays on or before the current date (whatever that is when the code is run):
SELECT
DATEADD(DAY, days_to_add, most_recent_monday) AS last_6_mondays
FROM (
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS most_recent_monday
) AS main_date
CROSS JOIN (
SELECT 0 AS days_to_add UNION ALL
SELECT -7 AS days_to_add UNION ALL
SELECT -14 UNION ALL
SELECT -21 UNION ALL
SELECT -28 UNION ALL
SELECT -35
) AS days_to_add
ORDER BY 1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 2, 2013 at 6:26 am
Thanks everyone for the suggestions!! I appreciate the help!
August 2, 2013 at 6:27 am
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
August 2, 2013 at 8:25 am
tdanley (8/2/2013)
Thanks this worked great! WOLFILLJwolfkillj (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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply