datediff working days only

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

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.

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

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

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

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.

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?  

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...
       

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

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..)

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.

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

....  
 

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' )

Select allOpen in new window

Thanks to all