TSQL: grouping customer orders by week

Question

I have a table with a collection of orders. The fields are:

• customerName (text)
• DateOfOrder (datetime).

I would like to show totals of orders per week per customer. I would like to have it arranged for the Friday of each week so that it looks like this:

all dates follow mm/dd/yyyy

"bobs pizza", 3/5/2010, 10
"the phone co",3/5/2010,5
"bobs pizza", 3/12/2010, 3
"the phone co",3/12/2010,11

Could somebody please show me how to do this?

Thanks

Answer 1

Make a field which you can group by more easily.

SELECT COUNT(OrderID), WeekStart
FROM 
(
 SELECT *, 
  dateadd(week, datediff(day,'20000107',yourDate) / 7, '20000107') AS WeekStart  
 FROM Orders
) o
GROUP BY WeekStart;

20000107 is a known Friday.

Answer 2

This isn't a complete order, but it will give you the week #. Just need to add grouping.

SELECT
Cast(DatePart(YEAR, dateadd(month, datediff(month, 0, DateOfOrder),0))
    AS nvarchar(20))
 + ' '
 + RIGHT('0' + Cast(DatePart(WK, DateOfOrder) AS nvarchar(20)), 2)
,CustomerName
FROM Orders

Answer 3

Just try to give this a go

DECLARE @Table TABLE(
        customerName VARCHAR(50),
        DateOfOrder DATETIME,
        Quantity FLOAT
        )

INSERT INTO @Table SELECT 'bobs pizza', '1/5/2010', 10      
INSERT INTO @Table SELECT 'bobs pizza', '3/5/2010', 10 
INSERT INTO @Table SELECT 'bobs pizza', '3/4/2010', 10 
INSERT INTO @Table SELECT 'the phone co','3/5/2010',5 
INSERT INTO @Table SELECT 'bobs pizza', '3/12/2010', 3 
INSERT INTO @Table SELECT 'the phone co','3/12/2010',11 
INSERT INTO @Table SELECT 'the phone co','3/13/2010',11 
INSERT INTO @Table SELECT 'the phone co','3/15/2010',11 


SELECT  DateFirday,
        DATENAME(dw, DateFirday),
        SUM(Quantity) Total
FROM    (
            SELECT  *,
                    DATEADD(week, DATEPART(WEEK, DateOfOrder) - 1, CAST('01 Jan' + CAST(DATEPART(year,DateOfOrder) AS VARCHAR(4)) AS DATETIME)) DateFirday
            FROM    @Table
        ) sub
GROUP BY DateFirday

Output

DateFirday                                             Total
----------------------- ------------------------------ ----------------------
2010-01-08 00:00:00.000 Friday                         10
2010-03-05 00:00:00.000 Friday                         25
2010-03-12 00:00:00.000 Friday                         25
2010-03-19 00:00:00.000 Friday                         11

If I missed something with the Friday dates, let me know so I can have a look.

Answer 4

This will work as long as there is no time component:

SELECT     DateOfOrder - DATEPART(dw, DateOfOrder) + 7 AS weekEndingDate, customerName, COUNT(*) AS totalOrders
FROM         Orders
GROUP BY DateOfOrder - DATEPART(dw, DateOfOrder) + 7, customerName

This assumes that DATEFIRST hasn't been changed. The subtraction will roll back to the previos Saturday, then + 7 gets to Friday.

If there is a time component, use one of the standard techniques to strip it off:

DATEADD(day, DATEDIFF(day, 0, DateOfOrder), 0) - DATEPART(dw, DateOfOrder) + 7

CAST(FLOOR(CAST(DateOfOrder as FLOAT)) as DATETIME) - DATEPART(dw, DateOfOrder) + 7

or, for SS2008

CAST(DateOfOrder AS date) - DATEPART(dw, DateOfOrder) + 7