Eliminating Weekend Dates in an expression

  • Im trying to evaluate certain events inside an arbitrary timeframe, and I want to make sure I evaluate the number of days that does not include weekend dates. Using this table setup :

    • TABLE dbo.orders --o
      ( OrderID (pk, int, NOT NULL),
      CustomerID varchar(5) NOT NULL,
      Sourceid (pk uniqueidentifier NOT NULL),
      Status varchar(50) null,
      Stockarrived datetime NULL,
      Ordershipped datetime NULL,
      Deliveryconfirmed datetime
      NULL CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC) );

      I will need to evaluate only the past 7 days not including weekend days , eliminating any instances of saturday or sunday. Im trying something to this effect :

      Select o.Customerid , o.Stockarrived, o.Status from orders as o where o.Customerid = '12345' and (DATEDIFF(dd,convert(datetime,o.Stockarrived,121),convert(datetime,getdate()) - (2 * DATEDIFF(wk,convert(datetime,o.Stockarrived,121),convert(datetime,getdate()) = 7 order by o.Stockarrived desc

      ..but need to make sure the date the stock arrives and the present date doesnt include the weekends

      ???
      Thanks in advance

  • Personally, I'd use a calendar table (Bones of SQL - The Calendar Table) and calculate from that..

    Then, using a Function, you could do something like this (Note, my Date table is not the same as that of in the above link, but the principle is the same):
    USE DevTestDB;
    GO

    CREATE FUNCTION WorkingDays (@StartDate DATE, @EndDate DATE)
    RETURNS TABLE
    AS
    RETURN (
      SELECT COUNT(DT.DateKey) AS WorkingDays,
            @StartDate AS StartDate,
            @EndDate AS EndDate
      FROM DateTable DT
      WHERE DT.[Date] BETWEEN @StartDate AND @EndDate
        AND DT.[Working Day] = 1 --I have a Working day bit on my Table, which also excludes public holidays
        --AND DT.[Day Name] NOT IN ('Saturday','Sunday') --Or you can do the simple way and exclude weekends
    );
    GO

    SELECT *
    FROM WorkingDays ('01-Jan-2017',GETDATE());
    /*
    Result:
    WorkingDays StartDate EndDate
    ----------- ---------- ----------
    44          2017-01-01 2017-03-02
    */
    GO

    DROP Function WorkingDays;
    GO

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom is right - a calendar table is the way to go.  You can use it again and again, and you can customise it with stuff like public holidays.  However, if your requirement is isolated and really as simple as you say, read on.

    First, let's format your code and get rid of the redundant CONVERTs.  (If you post a chunk of code that you've made no attempt to format then it'll be difficult to read, and many will take the view that it's not worth bothering with if you don't care about it.)
    SELECT
         o.Customerid
    ,    o.Stockarrived
    ,    o.Status
    FROM orders AS o
    WHERE o.Customerid = '12345'
    AND DATEDIFF(dd,o.Stockarrived,GETDATE()) - (2 * DATEDIFF(wk,o.Stockarrived,GETDATE()) = 7
    ORDER BY o.Stockarrived DESC;

    The next thing is to make the second part of your WHERE clause SARGable by avoiding functions on Stockarrived.  This will mean that the query optimizer will be able to consider seeking on any index on the column, and that the calculation only as to be done once - on the GETDATE() constant instead of (potentially) for every row in the table.  If it weren't for your weekend requirement, this would be quite simple:
    AND o.Stockarrived >= DATEADD(day,-7,GETDATE) AND o.Stockarrived < DATEADD(day,-6,GETDATE()) -- times between midnight a week ago and (but not including) midnight six days ago

    However, we do have the weeekend requirement, so we have to think of another way.  Note that your solution relies on the value of @@DATEFIRST.  Try SET LANGAUAGE 'English' and SET LANGUAGE 'British English' before running it, and see whether you get the same results each time.  What would be easier is to take off seven days, and then take off more days depending on what the day today is.  To make it look even cleaner, we could do that calculation before running the query.
    DECLARE @7DaysAgo datetime;
    SET @7DaysAgo = GETDATE() -7
        -    CASE DATENAME(dw,CURRENT_TIMESTAMP)
                WHEN 'Monday'    THEN 4
                WHEN 'Tuesday'   THEN 4
                WHEN 'Wednesday' THEN 2
                WHEN 'Thursday'  THEN 2
                WHEN 'Friday'    THEN 2
                WHEN 'Saturday'  THEN 3
                WHEN 'Sunday'    THEN 4
            END;
    SELECT
         o.Customerid
    ,    o.Stockarrived
    ,    o.Status
    FROM orders AS o
    WHERE o.Customerid = '12345'
    AND o.Stockarrived >= @7DaysAgo
    AND o.StockArrived < @7DaysAgo + 1
    ORDER BY o.Stockarrived DESC;

    In the absence of any sample data, I haven't tested this, so please make sure that you're happy my day calculations are correct.  And note that, while this solution doesn't rely on @@DATEFIRST, it does rely on your langauage being English or British English!

    John

  • Viewing 3 posts - 1 through 2 (of 2 total)

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