I have been tasked with returning the number of orders 'per week' within a given year and month (this is all happening within SSMS).

My data looks like something this:

I would like to get the results table to look something like this:

Currently I have the following SQL stored procedure that takes in a year (@year) and month (@month) as parameters:

SELECT
    SUM(CASE WHEN DateCreated BETWEEN (DATEFROMPARTS(@year, @month, 01)) AND (DATEFROMPARTS(@year, @month, 07)) 
             THEN 1 ELSE 0 END) AS Week1,
    SUM(CASE WHEN DateCreated BETWEEN (DATEFROMPARTS(@year, @month, 08)) AND (DATEFROMPARTS(@year, @month, 14)) 
             THEN 1 ELSE 0 END) AS Week2,
    SUM(CASE WHEN DateCreated BETWEEN (DATEFROMPARTS(@year, @month, 15)) AND (DATEFROMPARTS(@year, @month, 21)) 
             THEN 1 ELSE 0 END) AS Week3,
    SUM(CASE WHEN DateCreated BETWEEN (DATEFROMPARTS(@year, @month, 22)) AND (DATEFROMPARTS(@year, @month, 28)) 
             THEN 1 ELSE 0 END) AS Week4,
    SUM(CASE WHEN DateCreated BETWEEN (DATEFROMPARTS(@year, @month, 29)) AND (DATEFROMPARTS(@year, @month, 29)) 
             THEN 1 ELSE 0 END) AS Week5
FROM
    dbo.Orders

The above statement returns something that is close-ish to what I need but there are some issues, my result set looks like this:

So the biggest issues are of course the orientation and missing orders in week 5. My weeks are displayed along the x-axis instead of the y-axis, but also it seems that since the EOMONTH() function defaults the time stamp to midnight, any orders placed past 12am on the last day of the month are not taken into account.

Based on the research I have done thus far, I believe that I should be using some combination of DATEADD, DATEDIFF and COUNT (as opposed to SUM, so that I can do a GROUP BY) I have a good understanding of how these functions/statements work independently but am having trouble bringing them together to reach my goal. Any and all help would be much appreciated!

CodePudding user response：

I'm assuming:

1. that you want to start counting week 1 as starting on the first of the month and always being the first 7 days, and
2. week 5 should always be a partial week (i.e. the tail end of a month after the 28th), except in February of a non-leap year when it won't exist.
3. you want to see zero-weeks in the result set.

If all of these assumptions are true, I would use something like this:

DECLARE @Weeks TABLE (Week INT IDENTITY(1,1), SD DATETIME, ED DATETIME)
DECLARE @Date DATETIME = DATEFROMPARTS(@year,@month,1)
WHILE @Date < EOMONTH(DATEFROMPARTS(@year,@month,1))
BEGIN
    INSERT INTO @Weeks (SD, ED) 
        SELECT @Date, CASE WHEN DATEADD(DAY,7,@Date) > DATEADD(MONTH,1,DATEFROMPARTS(@year,@month,1)) THEN DATEADD(MONTH,1,DATEFROMPARTS(@year,@month,1)) ELSE DATEADD(DAY,7,@Date) END
    SET @Date = DATEADD(DAY,7,@Date)
END

SELECT w.Week, COUNT(ID) 'OrdersCount'
FROM @Weeks w 
    LEFT JOIN dbo.Orders ON w.SD <= DateCreated AND w.ED > DateCreated
GROUP BY w.Week

I've used a table variable to first build a list of weeks - this may not be strictly necessary (there are other ways to achieve this), but I like the flow. I recommend you use combination of < and start of next period as end date criteria, since BETWEEN is always inclusive and prone to issues with dates containing time portions in this kind of use case.

If you don't need to count weeks from the 1st day of the month in all cases and don't mind that zero-weeks won't be represented, then a really simple solution would be to use DATEPART(WEEK...), e.g.:

SELECT DATEPART(WEEK,DateCreated)-DATEPART(WEEK,DATEFROMPARTS(@year,@month,1)) 1 'Week', COUNT(ID) 'CountID' 
    FROM dbo.Orders 
    WHERE DateCreated >= DATEFROMPARTS(@year,@month,1) AND DateCreated < DATEADD(MONTH,1,DATEFROMPARTS(@year,@month,1))
    GROUP BY DATEPART(WEEK,DateCreated)

CodePudding user response：

You can use CASE to get a single week number then group by that number.

Use CROSS APPLY (VALUES to avoid repeating code

SELECT
    v.WeekNumber,
    TotalOrders = COUNT(*)
FROM
    dbo.Orders
CROSS APPLY (VALUES (
    CASE WHEN DateCreated >= DATEFROMPARTS(@year, @month, 1) AND DateCreated < DATEFROMPARTS(@year, @month, 8)
         THEN 1
         WHEN DateCreated >= DATEFROMPARTS(@year, @month, 8) AND DateCreated < DATEFROMPARTS(@year, @month, 15)
         THEN 2
         WHEN DateCreated >= DATEFROMPARTS(@year, @month, 15)) AND DateCreated < DATEFROMPARTS(@year, @month, 22)
         THEN 3
         WHEN DateCreated >= DATEFROMPARTS(@year, @month, 22)) AND DateCreated < DATEFROMPARTS(@year, @month, 29)
         THEN 4
         ELSE 5
    END
)) v(WeekNumber)

WHERE DateCreated >= DATEFROMPARTS(@year, @month, 1)
  AND DateCreated < DATEADD(month, 1, DATEFROMPARTS(@year, @month, 1))
GROUP BY
  v.WeekNumber;

Note the use of >= AND < for a half-open interval. This ensure that the whole of the last day is included.