Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

datediff working days only

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

Open in new window

0
skull52
Asked:
skull52
  • 7
  • 3
  • 3
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:

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

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Scott PletcherSenior DBACommented:
...
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
 
LowfatspreadCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
skull52Author Commented:
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
 
LowfatspreadCommented:
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
 
Scott PletcherSenior DBACommented:
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])
SELECT DATEADD(DAY, tally.tally, @startDate)
FROM  tally
WHERE tally BETWEEN 0 AND @numberOfDays
0
 
Scott PletcherSenior DBACommented:
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
 
skull52Author Commented:
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
 
LowfatspreadCommented:
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
 
Scott PletcherSenior DBACommented:
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' )

Open in new window

0
 
skull52Author Commented:
Thanks to all
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 7
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now