Eliminating Weekend Dates in an expression

1

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

2

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

3

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

Thanks- cascading whens thens does the trick!