Summarize data into Weekly basis (Wed - Tue)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Summarize data into Weekly basis (Wed - Tue)

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2008-07-21 : 17:27:57

I have a table which has data like

ORDER DATE, ORDERS RECEIVED, ORDERS SHIPPED
---------------------------------------------------------
7/2/2008, 1, 1
7/5/2008, 2, 1
7/7/2008, 2, 1
7/9/2008, 2, 2
7/10/2008, 1, 0
7/15/2008, 4, 2
7/17/2008, 3, 1
7/18/2008, 3, 2

Now I need a query which will divide the dates into weekly range
Start date will be 7/2/2008 a week from then 7/8/2008 and the count of orders and so on. The result set should look like
-------------------------------------------------------------

WEEK, #ORDERS RECEIVED, #ORDERS SHIPPED
-----------------------------------------------------
July 2-8, 5,3
July 9-15,7,4
July 16-22,6,3

--------------------------------------------------------------
The end date will be be tuesday always.
The start date will be wednesday.
Any help on this. here is the insert for the table.

CREATE TABLE #t1
(ORDERdate datetime, order_rcd int, order_ship int)

INSERT INTO #t1
VALUES('7/2/2008, 1, 1)
INSERT INTO #t1
VALUES('7/5/2008', 2, 1)
INSERT INTO #t1
VALUES('7/7/2008', 2, 1)
INSERT INTO #t1
VALUES('7/9/2008', 2, 2)
INSERT INTO #t1
VALUES('7/10/2008', 1, 0)
INSERT INTO #t1
VALUES('7/15/2008', 4, 2)
INSERT INTO #t1
VALUES('7/17/2008', 3, 1)
INSERT INTO #t1
VALUES('7/18/2008', 3, 2)

-----------------------------------------------------------------------------------------------
Ashley Rhodes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 17:44:36

[code]select datename(month, fromdate)
+ ' '
+ datename(day, fromdate)
+ '-'
+ case
when datename(month, fromdate) = datename(month, todate) then ''
else datename(month, todate) + ' '
end
+ datename(day, todate) as week,
rcv as [#orders received],
snt as [#orders shipped]
FROM (
select dateadd(day, datediff(day, '19000103', orderdate) / 7 * 7, '19000103') as fromdate,
dateadd(day, datediff(day, '19000103', orderdate) / 7 * 7, '19000109') as todate,
sum(order_rcd) as rcv,
sum(order_ship) AS snt
from #t1
group by datediff(day, '19000103', orderdate) / 7
) AS d[/code]

E 12°55'05.25"
N 56°04'39.16"

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2008-07-22 : 10:34:08

How do I order it by date in ascending order by date. I am not able to do that.

I changed your query to this:

SELECT WEEK,
SUM([#orders received]),
SUM([#orders shipped])

FROM
(
select datename(month, fromdate)
+ ' '
+ datename(day, fromdate)
+ '-'
+ case
when datename(month, fromdate) = datename(month, todate) then ''
else datename(month, todate) + ' '
end
+ datename(day, todate) as week,
rcv as [#orders received],
snt as [#orders shipped]
FROM (
select dateadd(day, datediff(day, '19000105', orderdate) / 7 * 7, '19000105') as fromdate,
dateadd(day, datediff(day, '19000105', orderdate) / 7 * 7, '19000111') as todate,
sum(order_rcd) as rcv,
sum(order_ship) AS snt
from #t1
group by #t1.ORDERdate,datediff(day, '19000105', orderdate) / 7
) AS d
) A

GROUP BY WEEK
ORDER BY WEEK

-----------------------------------------------------------------------------------------------
Ashley Rhodes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 10:44:16

E 12°55'05.25"
N 56°04'39.16"

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2008-07-22 : 12:14:12

I did that by adding weeknumber in from date and weeknumber in todate
and then ordering it by weeknumberfrom, weeknumberto

thanks for your help

-----------------------------------------------------------------------------------------------
Ashley Rhodes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 13:05:49

No need for double orders.
You know todate is always 6 days after fromdate, right?`

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources