SQLTeam.com | Weblogs | Forums

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

If you're always going back from the current date, I think you can just calc the min Stockarrived date and use that, without having the overhead of constantly manipulating the table column date:

DECLARE @Starting_Stockarrived_date datetime
/* back up 7 non-weekend days from the current date */
SET @Starting_Stockarrived_date = DATEADD(DAY, -8 + CASE DATEDIFF(DAY, 0, GETDATE()) % 7 WHEN 0 THEN 1 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END, GETDATE())
SELECT @Starting_Stockarrived_date

SELECT ...
FROM ...
WHERE o.Stockarrived >= @Starting_Stockarrived_date

Try something like:

WITH    Nums(Num)
          AS (
              SELECT
                    t.N * -1
                FROM
                    ( VALUES ( 1), ( 2), ( 2), ( 4), ( 5), ( 6), ( 7), ( 8), ( 9), ( 10), ( 11), ( 12), ( 13) ) t (N)
             ) ,
        DaysBetween(D, DW)
          AS (
              SELECT
                    DateAdd(d, Nums.Num, Cast(GetDate() AS date))
                  , DatePart(dw, Nums.Num)
                FROM
                    Nums
             ) ,
        Calendar(D, DW, N)
          AS (
              SELECT
                    DaysBetween.D
                  , DaysBetween.DW
                  , Row_Number() OVER (ORDER BY DaysBetween.D DESC)
                FROM
                    DaysBetween
                WHERE
                    DaysBetween.DW NOT IN (1, 7)
             )
     SELECT
            Calendar.D
          , Calendar.DW
          , Calendar.N
        FROM
            Calendar
        WHERE
            Calendar.N < 8;

Thanks- cascading whens thens does the trick!