Weekly report query. Is there easy solution?

[BenW]

Hi,

I have a table like this:

OrderDate (DateTime) Quantity (Int)
02/03/2012 100
02/03/2012 65
02/02/2012 90
02/01/2012 110
01/30/2012 130
01/29/2012 140
01/27/2012 150
01/26/2012 160
.....

I want to create a weekly report to show sum(Quantity) by week. But
the week here is not a nature week in SQL server. The week means that
from 7 days ago to today.

I know that I can use WHILE in a cursor to do this.
Is there any simple solution for this?

Thanks,

[Bob Barrows]

Post by BenW
Hi,
OrderDate (DateTime) Quantity (Int)
02/03/2012 100
02/03/2012 65
02/02/2012 90
02/01/2012 110
01/30/2012 130
01/29/2012 140
01/27/2012 150
01/26/2012 160
.....
I want to create a weekly report to show sum(Quantity) by week. But
the week here is not a nature week in SQL server. The week means that
from 7 days ago to today.
I know that I can use WHILE in a cursor to do this.
Is there any simple solution for this?

Hopefully you are using SQL 2005 or higher. This does the trick. Give it a
study and let us know if there's anything about it you don't understand. It
uses a recursive common table expression (CTE) to generate a table of
numbers from 0 to 52 (53 weeks - change the "52" to a higher or lower number
in both locations as needed) which are used to calculate week start and end
dates so the data can be grouped by those dates. To use it in your database,
delete the first CTE (everything from "Data" to "Nbrs", leaving the word
"Nbrs", but deleting the comma before the word so the statement begins "WITH
Nbrs (n) ... " and replace "Data" in the FROM clause with the name of your
table.

WITH Data (OrderDate,Quantity) AS (
select CAST('20120203' AS DATETIME),100
union all select CAST('20120203' AS DATETIME),65
union all select CAST('20120202' AS DATETIME),90
union all select CAST('20120201' AS DATETIME),110
union all select CAST('20120130' AS DATETIME),130
union all select CAST('20120129' AS DATETIME),140
union all select CAST('20120127' AS DATETIME),150
union all select CAST('20120126' AS DATETIME),160)
,Nbrs ( n ) AS (
SELECT 0 UNION ALL
SELECT 1 + n FROM Nbrs WHERE n < 52 )
, Weeks (WeekStartDate, WeekEndDate) AS (
SELECT
DATEADD(d,1,DATEADD(ww,-1*(n+1),DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP),0)
))
,DATEADD(d
,1,DATEADD(ww,-1*n,DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP),0)))
FROM Nbrs
)
SELECT WeekStartDate,SUM(Quantity) AS WeeklyTotal FROM Weeks AS w JOIN
Data AS d ON OrderDate >=WeekStartDate
AND OrderDate < WeekEndDate
GROUP BY WeekStartDate
ORDER BY WeekStartDate
OPTION (MAXRECURSION 52)

[Erland Sommarskog]

Post by BenW
OrderDate (DateTime) Quantity (Int)
02/03/2012 100
02/03/2012 65
02/02/2012 90
02/01/2012 110
01/30/2012 130
01/29/2012 140
01/27/2012 150
01/26/2012 160
......
I want to create a weekly report to show sum(Quantity) by week. But
the week here is not a nature week in SQL server. The week means that
from 7 days ago to today.

If I understand this correctly, this query could be your answer:

WITH CTE1 AS (
SELECT datediff(DAY, '20000101', OrderDate) AS orderdateno,
datediff(DAY, '20000101', getdate()) AS todayno
FROM Orders
), CTE2 AS (
SELECT orderdateno % 7 AS order_wd, orderdateno / 7 AS order_week,
todayno % 7 AS today_wd
FROM CTE1
), CTE3 AS (
SELECT CASE WHEN order_wd > today_wd
THEN order_week + 1
ELSE order_week
END AS order_week, today_wd
FROM CTE2
), CTE4 AS (
SELECT dateadd(DAY, 7*order_week + today_wd, '20000101') AS
adjusted_orderdate
FROM CTE3
)
SELECT adjusted_orderdate, COUNT(*)
FROM CTE4
GROUP BY adjusted_orderdate
ORDER BY adjusted_orderdate


The idea is to compute an adjusted orderdate, so that an order placed on
Friday, is display under today's day, whereas an order from Monday Jan 30th
appears as Jan 31st.

I did not have an good test data, so the query may have one-off errors. You
need to test!