SQL Hurdle - SQL Server 2008

Question

The following query returns the total amount of orders, per week, for the past 12 months (for a specific customer):

SELECT DATEPART(year, orderDate) AS [year],
       DATEPART(month, orderDate) AS [month],
       DATEPART(wk, orderDate) AS [week],
       COUNT(1) AS orderCount
FROM dbo.Orders (NOLOCK)
WHERE customerNumber = @custnum
AND orderDate >= DATEADD(month, -12, GETDATE())
GROUP BY DATEPART(year, orderDate),
         DATEPART(wk, orderDate),
         DATEPART(month, orderDate)
ORDER BY DATEPART(year, orderDate),
         DATEPART(wk, orderDate)

This returns results like:

year    month    week    orderCount
2008      1        1         23
2008      3        12        5

...

As you can see, only weeks that have orders for this customer will be returned in the resultset. I need it to return a row for every week in the past 12 months... if no order exists in the week then returning 0 for orderCount would be fine, but I still need the year, week, and month. I can probably do it by creating a separate table storing the weeks of the year, then left outer join against it, but would prefer not to. Perhaps there's something in SQL that can accomplish this? Can I create a query using built in functions to return all the weeks in the past 12 months with built in SQL functions? I'm on SQL Server 2008.

Edit: Using Scott's suggestion I posted the query solving this problem below.

Answer 1

In the past I've done this using the table approach that you mention, the other way was to create a table function that I could pass arguments to specifying the start and end range that I wanted and it would build results dynamically to save needing to add a table with all the data.

Answer 2

The table method you are already aware is the best way to go. Not only does it give you alot of control, but it is the best performing.

You could write sql code (user function) to do this, but it won't be as flexible. RDBMs are made for handling sets.

Answer 3

You could join to a recursive CTE - something like below should give you a start...

WITH MyCte AS    
    (SELECT MyWeek = 1     
    UNION ALL     
    SELECT MyWeek + 1     
    FROM MyCte     
    WHERE MyWeek < 53)
SELECT  MyWeek, 
     DATEPART(year, DATEADD(wk, -MyWeek, GETDATE())),
     DATEPART(month, DATEADD(wk, -MyWeek, GETDATE())),
     DATEPART(wk, DATEADD(wk, -MyWeek, GETDATE()))
FROM    MyCte

Answer 4

Solution using CTE: (thanks to Scott's suggestion)

;WITH MyCte AS    
    (SELECT     MyWeek = 1     
    UNION ALL     
    SELECT      MyWeek + 1     
    FROM        MyCte     
    WHERE       MyWeek < 53)

SELECT  myc.[year],
     myc.[month],
     myc.[week],
     isnull(t.orderCount,0) AS orderCount,
     isnull(t.orderTotal,0) AS orderTotal
FROM (SELECT  MyWeek, 
     DATEPART(year, DATEADD(wk, -MyWeek, GETDATE())) AS [year],
     DATEPART(month, DATEADD(wk, -MyWeek, GETDATE())) AS [month],
     DATEPART(wk, DATEADD(wk, -MyWeek, GETDATE())) AS [week]
     FROM    MyCte) myc

     LEFT OUTER JOIN 

     (SELECT DATEPART(year, orderDate) AS [year],
         DATEPART(month, orderDate) AS [month],
         DATEPART(wk, orderDate) AS [week],
         COUNT(1) AS orderCount,
         SUM(orderTotal) AS orderTotal
     FROM dbo.Orders (NOLOCK)
     WHERE customerID = @custnum
     AND orderDate >= DATEADD(month, -12, GETDATE())
     GROUP BY DATEPART(year, ODR_DATE),
        DATEPART(wk, orderDate),
        DATEPART(month, orderDate)) t ON t.[year] = myc.[year] AND t.[week] = myc.[week]
ORDER BY myc.[year],
      myc.[week]

Edit: just noticed one week is being duplicated (2 records for the same week)... probably a simple logical error... disregard... ID-10-T... apparently a week can span months... who would have known lol