May 5, 2009 at 4:13 am
Hi Guys
I have a table containing order information.
An example is shown below (trimmed down version):
OrderID, DateEntered
1, 2009-04-06
2, 2009-04-06
3, 2009-04-08
4, 2009-04 10
5, 2009-04-12
6, 2009-04-13
7, 2009-04-15
8,2009-04-16
Etc.
What I need to do is produce a query that will return a count of orders by week commencing date. With the first day being a Monday.
Eg.
WeekCommencing, Count
2009-04-06, 4
2009-04-13, 20
2009-04-20, 25
2009-04-27, 33
Etc.
Is this possible to do?
Thanking you in advance!!!
May 5, 2009 at 4:33 am
Hi
One way would be to fake the date with DATEADD in case of sundays:
DECLARE @t TABLE (Id INT NOT NULL, DateEntered DATETIME)
INSERT INTO @t
SELECT 1, '2009-04-06'
UNION ALL SELECT 2, '2009-04-06'
UNION ALL SELECT 3, '2009-04-08'
UNION ALL SELECT 4, '2009-04-10'
UNION ALL SELECT 5, '2009-04-12'
UNION ALL SELECT 6, '2009-04-13'
UNION ALL SELECT 7, '2009-04-15'
UNION ALL SELECT 8, '2009-04-16'
; WITH
weeks (Id, DateEntered) AS
(
SELECT
Id,
CASE WHEN DATEPART(WEEKDAY, DateEntered) = 1
THEN DATEADD(DAY, -1, DateEntered)
ELSE DateEntered
END
FROM @t
)
SELECT
DATEPART(YEAR, DateEntered) OrderYear,
DATEPART(WEEK, DateEntered) OrderWekk,
COUNT(*)
FROM weeks
GROUP BY
DATEPART(YEAR, DateEntered),
DATEPART(WEEK, DateEntered)
If you generally need Monday as first day of your week you can use "SET DATEFIRST" to change the settings of your current connection:
SET DATEFIRST 1
SELECT
DATEPART(YEAR, DateEntered) OrderYear,
DATEPART(WEEK, DateEntered) OrderWekk,
COUNT(*)
FROM @t
GROUP BY
DATEPART(YEAR, DateEntered),
DATEPART(WEEK, DateEntered)
Greets
Flo
May 5, 2009 at 4:40 am
The set option does the trick in the below code. Set it accordingly.
Insert into #Ord (OrderId, DateEntered)
Values(1, '2009-04-06')
,(2, '2009-04-06')
,(3, '2009-04-08')
,(4, '2009-04-10')
,(5, '2009-04-12')
,(6, '2009-04-13')
,(7, '2009-04-15')
,(8, '2009-04-16')
SET DATEFIRST 1;
SELECT FirstDayOfWeek, COUNT(*)
FROM (
SELECT *, DATEADD(d, -(DATEPART(WEEKDAY,DateEntered)-1) , DateEntered) as FirstDayOfWeek
FROM #Ord
) t
GROUP BY FirstDayOfWeek
May 5, 2009 at 4:49 am
Hey Guys
Thanks for that!!!:-D
May 5, 2009 at 5:02 am
Hey there,
I don't normally reply where the thread-starter doesn't post a working test-rig set-up script (in SQL!) or where (s)he hasn't shown the results of his or her efforts so far.
Perhaps you would bear that in mind for next time?
Here is yet another way, and another example of posting a set-up script too...:-D
Note that you can do this without setting DATEFIRST explicitly. You just need to take account of the current setting of @@DATEFIRST. I'll leave that as an exercise for you!
Cheers,
Paul
USE tempdb;
--DROP TABLE dbo.Data;
CREATE TABLE dbo.Data
(
order_idINTNOT NULLPRIMARY KEY,
date_enteredDATETIMENOT NULL,
);
INSERTdbo.Data
(order_id, date_entered)
SELECT1, '2009-04-06' UNION ALL
SELECT2, '2009-04-06' UNION ALL
SELECT3, '2009-04-08' UNION ALL
SELECT4, '2009-04-10' UNION ALL
SELECT5, '2009-04-12' UNION ALL
SELECT6, '2009-04-13' UNION ALL
SELECT7, '2009-04-15' UNION ALL
SELECT8, '2009-04-16';
SELECT*
FROMdbo.Data
-- Monday is the first day of the week
SET DATEFIRST 1;
;WITHDatesMovedToMonday (date_entered_monday, order_id)
AS(
SELECTDATEADD(DAY, 0 - DATEPART(WEEKDAY, date_entered), date_entered) + 1,
order_id
FROMdbo.Data
)
SELECTdate_entered_monday,
COUNT_BIG(*) AS order_count
FROMDatesMovedToMonday
GROUPBY
date_entered_monday
ORDERBY
date_entered_monday;
DROP TABLE dbo.Data;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 5, 2009 at 7:34 am
While I have not tested any of the solutions presented I am pretty sure, based on past experience with these posters, that they will all work fine, but
I usually recommend using a calendar table whenever someone posts a question like this. Here's a link that explains setting up a calendar table.
I think every DBA\Developer should have at least a numbers/tally table and a calendar table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 5, 2009 at 8:30 am
This code will work the same no matter what the setting of DATEFIRST is.
create table #t (
order_id int not null primary key,
date_entered datetime not null )
insert #t (order_id, date_entered)
select 1, '2009-04-06' union all select 2, '2009-04-06' union all
select 3, '2009-04-08' union all select 4, '2009-04-10' union all
select 5, '2009-04-12' union all select 6, '2009-04-13' union all
select 7, '2009-04-15' union all select 8, '2009-04-22' union all
select 9, '2009-04-24' union all select 10, '2009-04-16'
select
dateadd(dd,(datediff(dd,0,a.date_entered)/7)*7,0) as Week_Start_Date,
count(*) as Order_count
from#t a
group by dateadd(dd,(datediff(dd,0,a.date_entered)/7)*7,0)
order by dateadd(dd,(datediff(dd,0,a.date_entered)/7)*7,0)
drop table #t
Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
This a function for creating a calendar table:
Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
Much more information on how to work with datetime in SQL Server
Date/Time Info and Script Links
May 5, 2009 at 9:17 am
So much for me leaving it as an exercise for the OP then!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply