Datediff (exclude holidays - no function)

Question

Datediff (exclude holidays - no function)

clouddeveloper

SSC Enthusiast

Points: 172
More actions
August 18, 2021 at 2:58 pm

#3921411
I'm counting the days between two dates using DateDiff. In my query I'm excluding weekends, now, I need to excluded the company holidays. How can I accomplish this without a function and just do it within the query? I have a holiday table that lists all of the company holidays.
Calcs. the start date and end date excluding weekends. I need to include the exclusion of holidays that are in a holiday table
```
select * ,
(DATEDIFF(dd, JobStartDate, JobEndDate) + 1) - DATEDIFF(wk, 
                       JobStartDate, JobEndDate) * 2 - (CASE WHEN DATENAME(dw, JobStartDate) = 'Sunday' THEN 1 ELSE 0 END) - (CASE WHEN DATENAME(dw, 
                        JobEndDate) = 'Saturday' THEN 1 ELSE 0 END) AS DaysOver
 from WorkDue
```
September 8, 2021 at 7:08 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101095 More actions · Answer 1

You should just be able to add this to the end of your existing calc:

(DATEDIFF...
- (SELECT COUNT(*) FROM dbo.Holidays H WHERE H.date BETWEEN JobStartDate AND JobEndDate)
AS ...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 2

The Julian or ordinal business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.

CREATE TABLE Calendar

(cal_date DATE NOT NULL PRIMARY KEY,

julian_business_nbr INTEGER NOT NULL,

...);

INSERT INTO Calendar

VALUES ('2007-04-05', 42),

('2007-04-06', 43), -- good Friday

('2007-04-07', 43),

('2007-04-08', 43), -- Easter Sunday

('2007-04-09', 44),

('2007-04-10', 45); --Tuesday

To compute the business days from Thursday of this week to next Tuesdays:

SELECT (C2.julian_business_nbr - C1.julian_business_nbr)

FROM Calendar AS C1, Calendar AS C2

WHERE C1.cal_date = '2007-04-05',

AND C2.cal_date = '2007-04-10';

your mindset is wrong. SQL is a database language, not a computational language. Given how messy the calendar is, trying to do this with function calls is doomed to failure or unnecessary complexities.

Please post DDL and follow ANSI/ISO standards when asking for help.