Solved

# datediff working days only

Posted on 2011-10-26
230 Views
I have a script that purges sales orders that are older than 6 days but I want it to count only working day (Monday-Friday) and ignore the weekends.
``````SELECT     TOP (100) PERCENT SOPNUMBE AS ORDER_NMBR, DOCDATE AS ORDER_DATE, BACHNUMB AS BATCH_NAME, CUSTNMBR AS CUST_NMBR,
CUSTNAME AS CUST_NAME
FROM         dbo.SOP10100
WHERE     (SOPNUMBE NOT IN
(SELECT     SOPNUMBE
FROM          dbo.SSG_V_SOP_SavedOrders)) AND (SOPTYPE = 2) AND (NOT (BACHNUMB = '')) AND (DATEDIFF(Day, DOCDATE, GETDATE()) > 6)
ORDER BY ORDER_DATE DESC
``````
0
Question by:skull52

LVL 142

Expert Comment

this will be only effectively done with a calendar table, where you can find the proper information quickly without ugly code:
http://www.aspfaq.com/2519
0

LVL 68

Expert Comment

If you are not concerned about holidays, etc., easiest is a UDF (user-defined function) to get the number of work days.  If you wanted, you could also add hard-coded holidays to the UDF.  Will post UDF asa I can.
0

LVL 68

Accepted Solution

``````CREATE FUNCTION dbo.CalcWorkDays (
@start_date datetime,
@end_date datetime
)
RETURNS int
AS
BEGIN
DECLARE @days int
DECLARE @weekday int
SELECT @days = DATEDIFF(DAY, @start_date, @end_date),
@weekday = DATEPART(WEEKDAY, @start_date)
RETURN (
@days / 7 * 5 + @days % 7
- CASE WHEN 6 BETWEEN @weekday AND @weekday + @days % 7 - 1 THEN 1 ELSE 0 END
- CASE WHEN 7 BETWEEN @weekday AND @weekday + @days % 7 - 1 THEN 1 ELSE 0 END
)
END --FUNCTION
``````
0

LVL 68

Expert Comment

...
WHERE     (SOPNUMBE NOT IN
(SELECT     SOPNUMBE
FROM          dbo.SSG_V_SOP_SavedOrders)) AND (SOPTYPE = 2) AND (NOT (BACHNUMB = '')) AND dbo.CalcWorkDays(DOCDATE, GETDATE()) > 6)
0

LVL 50

Expert Comment

you are better off having a business calendar table which classifies days as you desired...

that way public holidays etc can also be accomodated...
0

LVL 68

Expert Comment

Really depends on your performance needs.  For absolute max perf, you can do the equivalent of what the function is doing in-line.  For next best, use the function.  Least performance will be a calendar table, but it is relatively easy to set up and use.
0

Author Comment

Angel, thanks fro the link but it seems way more complicated than I need, it would be nice to exclude holidays with the use of a calender table if it could be simplified more that that article conveys.

Scott, I will try your suggestion and you mentioned that a calender table could be setup relatively easy, if it is not to much of a pain could you explain?
0

LVL 50

Expert Comment

basically pick a startdate then auto generate several years worth of data from that...

like this

select Thedate,case when month(thedate)=12 and datepart(dd,thedate)=25 then 'H'
else
case datename(dw,thedate)
when 'saturday' then 'W' when 'Sunday' then 'W' else 'B' end
end as DayType
,year(Thedate) as CalendarYear
,Month(thedate) as CalendarMonth
,....
from ( Select dateadd(d,v2.(number*1000)+v1.number,'20000101') as Thedate
from master.dbo.spt_values as v1
cross join master.dbo.spt_values as v2
where v1.type='p' and v2.type='p'
and v1.number between 0 and 999
and v2.number between 0 and 10
) as x

will create 10000 days worth of dates...

0

LVL 68

Expert Comment

You *must* use your own tally / sequential numbers table or the ROW_NUMBER() function -- don't rely on every value between 0 and 999 always being in a certain system table just because it happens to be today!

I can provide code to create a tally table if you don't have one.

DECLARE @startDate datetime
DECLARE @numberOfDays int

SET @startDate = '20100101' --you can decide if you need historical dates or not
SET @numberOfDays = 1500 --gen roughly 4 yrs worth of dates

INSERT INTO calendarTable ([date])
FROM  tally
WHERE tally BETWEEN 0 AND @numberOfDays
0

LVL 68

Expert Comment

Obviously you need some flags in the calendarTable row to indicate holidays, etc..

CREATE TABLE celanderTable (
[date] date,
isWorkDay bit,
isHoliday bit,
isWeekend bit
)

Then you UPDATE the bits using logical similar to above.  Naturally you will have to code in the holidays as they are custom for every company.

(Why separate bits for each?  Because it could be that certain holidays may become workdays, same for certain weekend days, under special circumstances.  For example, a natural disaster forces odd scheduling, etc..)
0

Author Comment

Scott, your solution worked thanks. I am interested in the calender table solution though.  Could your provide the tally table code and how would i implement it with my current application, also how would i exclude holidays, sorry for all the questions but I can see a need for something lit that.
0

LVL 50

Expert Comment

inline tally table

;with cte10 as (select 0 as n union all select 1 union all select 2 union select 3
union all select 4 union all select 5 union select 6 union select 7 union all select 8
union all select 9)
, cte100 as (
select a.n + (b.n*10) as n
from cte as a,cte as b)
,cte10K as (select a.n + (b.n*100) as n
from cte100 as a,cte100 as b)
select n
order by 1

....

0

LVL 68

Expert Comment

Tally table code below.
``````IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally
CREATE TABLE dbo.Tally (
tally int,
CONSTRAINT Tally__PK PRIMARY KEY (tally) WITH FILLFACTOR = 100
)

DECLARE @digits TABLE (
digit int PRIMARY KEY
)
INSERT INTO @digits
SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9 ORDER BY digit

INSERT INTO Tally (tally)
SELECT digit + tens + hundreds + thousands + tenthousands AS tally
FROM @digits ones
CROSS JOIN (
SELECT digit * 10 AS tens
FROM @digits
) AS tens
CROSS JOIN (
SELECT digit * 100 AS hundreds
FROM @digits
) AS hundreds
CROSS JOIN (
SELECT digit * 1000 AS thousands
FROM @digits
) AS thousands
CROSS JOIN (
SELECT digit * 10000 AS tenthousands
FROM @digits
) AS tenthousands
ORDER BY tally
ALTER INDEX Tally__PK ON Tally REBUILD
DBCC SHOWCONTIG ( 'tally' )
``````
0

Author Comment

Thanks to all
0

## Featured Post

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidtâ€¦
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦