Count based on weekend days

  • I have to count the number of orders created on weekends and the criteria is that any order that drops between 1 pm Friday and 1 pm Monday needs to be counted as an order for Monday

    Here is what I have (Thanks to another  post I made on this Forum)

    select CAST(DATEADD(HOUR, 11, createdate) AS Date) OrderDate, count(1) CreatedCount
    from Orders 
    where Createdate > getdate() - 12
    and DATEPART(dw,CAST(DATEADD(HOUR, 11, createdate) AS Date)) in (6,7,1)
    group by CAST(DATEADD(HOUR, 11, createdate) AS Date)

    OrderDate    CreatedCount
    2017-12-29    4
    2017-12-30    3
    2017-12-31    2
    2018-01-01    2
    2018-01-05    5
    2018-01-06    3
    2018-01-07    3
    2018-01-08    4

    How can I roll it up to this?

    OrderDate    CreatedCount
    2018-01-01    11
    2018-01-08    15

    Thank you

  • Please post consumable data

  • Here you go...

    CREATE TABLE [dbo].[Orders](
        [OrderNo] [nvarchar](50) NOT NULL,
        [CreateDate] datetime NOT NULL,
    )
    GO

    INSERT INTO Orders VALUES (6106,'2017-12-28 10:28:11');
    INSERT INTO Orders VALUES (6106,'2017-12-28 18:28:11');
    INSERT INTO Orders VALUES (1889,'2017-12-29 11:28:11');
    INSERT INTO Orders VALUES (1799,'2017-12-29 11:28:11');
    INSERT INTO Orders VALUES (6106,'2017-12-29 17:28:11');
    INSERT INTO Orders VALUES (27889,'2017-12-30 11:28:11');
    INSERT INTO Orders VALUES (2789,'2017-12-30 11:20:11');
    INSERT INTO Orders VALUES (3889,'2017-12-30 19:28:11');
    INSERT INTO Orders VALUES (1791,'2017-12-31 11:10:11');
    INSERT INTO Orders VALUES (1782,'2018-01-01 10:15:10');
    INSERT INTO Orders VALUES (173,'2018-01-01 10:28:14');
    INSERT INTO Orders VALUES (1784,'2018-01-02 10:15:10');
    INSERT INTO Orders VALUES (175,'2018-01-02 10:28:14');
    INSERT INTO Orders VALUES (176,'2018-01-03 17:15:10');
    INSERT INTO Orders VALUES (177,'2018-01-03 14:28:14');
    INSERT INTO Orders VALUES (178,'2018-01-04 19:10:10');
    INSERT INTO Orders VALUES (179,'2018-01-05 10:28:14');
    INSERT INTO Orders VALUES (171,'2018-01-05 19:10:10');
    INSERT INTO Orders VALUES (1710,'2018-01-06 09:28:14');
    INSERT INTO Orders VALUES (1712,'2018-01-07 05:10:10');
    INSERT INTO Orders VALUES (1713,'2018-01-07 05:28:14');
    INSERT INTO Orders VALUES (1714,'2018-01-07 09:30:14');
    INSERT INTO Orders VALUES (1715,'2018-01-08 03:10:10');
    INSERT INTO Orders VALUES (1716,'2018-01-08 04:18:14');
    INSERT INTO Orders VALUES (1717,'2018-01-08 08:10:14');
    INSERT INTO Orders VALUES (1718,'2018-01-08 09:21:14');
    INSERT INTO Orders VALUES (1719,'2018-01-08 20:21:14');

    select CAST(DATEADD(HOUR, 11, createdate) AS Date) OrderDate, count(1) CreatedCount
    from Orders
    where Createdate > getdate() - 12
    and DATEPART(dw,CAST(DATEADD(HOUR, 11, createdate) AS Date)) in (6,7,1)
    group by CAST(DATEADD(HOUR, 11, createdate) AS Date)

    OrderDate    CreatedCount
    2017-12-29    3
    2017-12-30    3
    2017-12-31    2
    2018-01-01    2
    2018-01-05    2
    2018-01-06    2
    2018-01-07    3
    2018-01-08    4

    I need to roll it up to
    2018-01-01    10
    2018-01-08    11

  • Probably a better way to accomplish this but with limited time at work here is one way of doing it:

    SELECT
      CASE WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) IN ('Friday','Saturday','Sunday','Monday') THEN
           DATEADD(DAY,CASE WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Friday'
                                 THEN 3
                            WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Saturday'
                                 THEN 2
                            WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Sunday'
                                 THEN 1
                                 ELSE 0
                       END,CAST(DATEADD(HOUR,11,CreateDate) AS DATE))
           ELSE CAST(DATEADD(HOUR,11,CreateDate) AS DATE)
      END OrderDate
      , COUNT(OrderNo) OrderCount
    FROM [dbo].[Orders]
    GROUP BY
      CASE WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) IN ('Friday','Saturday','Sunday','Monday') THEN
           DATEADD(DAY,CASE WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Friday' THEN 3 WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Saturday' THEN 2 WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Sunday' THEN 1 ELSE 0 END,CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) 
           ELSE CAST(DATEADD(HOUR,11,CreateDate) AS DATE)
      END;

  • Lynn Pettis - Tuesday, January 9, 2018 10:03 AM

    Probably a better way to accomplish this but with limited time at work here is one way of doing it:

    SELECT
      CASE WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) IN ('Friday','Saturday','Sunday','Monday') THEN
           DATEADD(DAY,CASE WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Friday'
                                 THEN 3
                            WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Saturday'
                                 THEN 2
                            WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Sunday'
                                 THEN 1
                                 ELSE 0
                       END,CAST(DATEADD(HOUR,11,CreateDate) AS DATE))
           ELSE CAST(DATEADD(HOUR,11,CreateDate) AS DATE)
      END OrderDate
      , COUNT(OrderNo) OrderCount
    FROM [dbo].[Orders]
    GROUP BY
      CASE WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) IN ('Friday','Saturday','Sunday','Monday') THEN
           DATEADD(DAY,CASE WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Friday' THEN 3 WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Saturday' THEN 2 WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Sunday' THEN 1 ELSE 0 END,CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) 
           ELSE CAST(DATEADD(HOUR,11,CreateDate) AS DATE)
      END;

    This worked perfectly. Thanks!!

  • I prefer to avoid language dependency when possible, and therefore use a "standard" relative day# for day of week rather than a day name.

    SELECT CreateDayAdjusted, COUNT(*)
    FROM #Orders
    CROSS APPLY (
      SELECT CAST(DATEADD(HOUR, 11, CreateDate) AS date) AS CreateDay
    ) AS ca1
    CROSS APPLY (
      SELECT DATEDIFF(DAY, 0, CreateDay) % 7 AS CreateDayOfWeek,
       4 AS Friday /*0=Mon;1=Tue;...;4=Fri;5=Sat;6=Sun*/
    ) AS ca2
    CROSS APPLY (
      SELECT DATEADD(DAY, CASE WHEN CreateDayOfWeek >= Friday
       THEN (7 - CreateDayOfWeek) ELSE 0 END, CreateDay) AS CreateDayAdjusted
    ) AS ca3
    GROUP BY CreateDayAdjusted

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • ScottPletcher - Wednesday, January 10, 2018 11:16 AM

    I prefer to avoid language dependency when possible, and therefore use a "standard" relative day# for day of week rather than a day name.

    SELECT CreateDayAdjusted, COUNT(*)
    FROM #Orders
    CROSS APPLY (
      SELECT CAST(DATEADD(HOUR, 11, CreateDate) AS date) AS CreateDay
    ) AS ca1
    CROSS APPLY (
      SELECT DATEDIFF(DAY, 0, CreateDay) % 7 AS CreateDayOfWeek,
       4 AS Friday /*0=Mon;1=Tue;...;4=Fri;5=Sat;6=Sun*/
    ) AS ca2
    CROSS APPLY (
      SELECT DATEADD(DAY, CASE WHEN CreateDayOfWeek >= Friday
       THEN (7 - CreateDayOfWeek) ELSE 0 END, CreateDay) AS CreateDayAdjusted
    ) AS ca3
    GROUP BY CreateDayAdjusted

    I did say that there is probably a better way.

  • Lynn Pettis - Wednesday, January 10, 2018 12:11 PM

    ScottPletcher - Wednesday, January 10, 2018 11:16 AM

    I prefer to avoid language dependency when possible, and therefore use a "standard" relative day# for day of week rather than a day name.

    SELECT CreateDayAdjusted, COUNT(*)
    FROM #Orders
    CROSS APPLY (
      SELECT CAST(DATEADD(HOUR, 11, CreateDate) AS date) AS CreateDay
    ) AS ca1
    CROSS APPLY (
      SELECT DATEDIFF(DAY, 0, CreateDay) % 7 AS CreateDayOfWeek,
       4 AS Friday /*0=Mon;1=Tue;...;4=Fri;5=Sat;6=Sun*/
    ) AS ca2
    CROSS APPLY (
      SELECT DATEADD(DAY, CASE WHEN CreateDayOfWeek >= Friday
       THEN (7 - CreateDayOfWeek) ELSE 0 END, CreateDay) AS CreateDayAdjusted
    ) AS ca3
    GROUP BY CreateDayAdjusted

    I did say that there is probably a better way.

    Wow..This is something else!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply