Clever Geek Handbook

SQL Hurdle - SQL Server 2008

The following query returns the total amount of orders per week for the last 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 result set. I need it to return a string every week for the last 12 months ... if no order exists for that week, then returning 0 for orderCount would be fine, but I still need the year, week, and month. I could possibly do this by creating a separate table storing the weeks of the year and then leaving the outer join against it, but would rather not. Maybe something in SQL that can do this? Can I create a query using built-in functions to return all weeks in the last 12 months with built-in SQL functions? I am on SQL Server 2008.

Edit: Using Scott's suggestion, I posted a request addressing this issue below.

+2


source to share


4 answers


You can join a recursive CTE - something like below should get you started ...



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
+4


source


The table method you already know is the best way. Not only does it give you a lot of control, but it is the best result.



You can write sql (custom function) code to do this, but it won't be as flexible. RDBMs are built to handle collections.

+2


source


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 that one week is duplicated (2 entries for the same week) ... maybe a simple logical error ... ignore ... ID-10-T ... apparently a week can span months ... who would know lol

+1


source


I have done this in the past using the tabular approach you mentioned, another way was to create a table function where I could pass arguments to specify the start and end range I wanted and it would dynamically build the results to keep the table added with all the data.

0


source






More articles:


All Articles