Thread: Calculate Date Difference, Excluding Dates

1. Registered User
Join Date
Aug 2012
Posts
4

Unanswered: Calculate Date Difference, Excluding Dates

I have already seen stored procedures that can calculate a difference in dates, excluding the weekends. I am wondering if anyone has seen an extension of such a SQL query to exclude not only weekends, but other dates as well. We have a table of "holidays" (not necessarily standard holidays), and I am wondering if there is a way to exclude them from the calculation.

Glenn Rosenthal

2. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
How about like this expression?

Code:
DATEDIFF(day , @startDate , @endDate)
- (SELECT COUNT(holiday)
FROM  holidays
WHERE holiday BETWEEN @startDate
AND @endDate
)

3. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
...won't work if you are excluding holidays and weekends, and the holiday falls on a weekend.

4. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
I assumed that weekends also included in a table of "holidays".

5. Registered User
Join Date
Aug 2012
Posts
4
No, weekends would not be in the table for "holidays".

6. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Here's a function you can use. You could easily replace the hard-code holiday list with a reference to a table of Holidays. Use the "IsWorkDay" field to determine whether you dress business casual or lounge around in your pajamas on any given date.

Code:
create function [dbo].[DateRange](@StartDate date, @EndDate date)
returns table
--Test parameters
--declare	@StartDate date
--declare	@EndDate date
--set		@StartDate = GETDATE()
--set		@EndDate = '2013-01-01'
return
with Holidays as
(
select '2011-01-03' as HolidayDate, 'New Years' as Holiday
union select '2011-05-30' as HolidayDate, 'Memorial Day' as Holiday
union select '2011-07-04' as HolidayDate, 'Independence Day' as Holiday
union select '2011-09-05' as HolidayDate, 'Labor Day' as Holiday
union select '2011-11-24' as HolidayDate, 'Thanksgiving' as Holiday
union select '2011-11-25' as HolidayDate, 'Thanksgiving' as Holiday
union select '2011-12-26' as HolidayDate, 'Christmas Day' as Holiday

union select '2012-01-02' as HolidayDate, 'New Years' as Holiday
union select '2012-05-28' as HolidayDate, 'Memorial Day' as Holiday
union select '2012-07-04' as HolidayDate, 'Independence Day' as Holiday
union select '2012-09-03' as HolidayDate, 'Labor Day' as Holiday
union select '2012-11-22' as HolidayDate, 'Thanksgiving' as Holiday
union select '2012-11-23' as HolidayDate, 'Thanksgiving' as Holiday
union select '2012-12-25' as HolidayDate, 'Christmas Day' as Holiday

union select '2013-01-01' as HolidayDate, 'New Years' as Holiday
union select '2013-05-27' as HolidayDate, 'Memorial Day' as Holiday
union select '2013-07-04' as HolidayDate, 'Independence Day' as Holiday
union select '2013-09-02' as HolidayDate, 'Labor Day' as Holiday
union select '2013-11-28' as HolidayDate, 'Thanksgiving' as Holiday
union select '2013-11-29' as HolidayDate, 'Thanksgiving' as Holiday
union select '2013-12-25' as HolidayDate, 'Christmas Day' as Holiday
),
DateList as
(
select	Number as DateNumber,
DATEADD(day, number, @StartDate) as DateValue
where	DATEADD(day, number, @StartDate) <= @EndDate
)
select	DateList.DateNumber,
DateList.DateValue,
datepart(weekday, DateList.DateValue) DayOfWeek,
datepart(day, DateList.DateValue) DayOfMonth,
datepart(DAYOFYEAR, DateList.DateValue) DayOfYear,
case when datepart(weekday, DateList.DateValue) between 2 and 6 then 1 else 0 end as IsWeekDay,
case when (datepart(weekday, DateList.DateValue) between 2 and 6 and Holidays.HolidayDate is null) then 1 else 0 end as IsWorkDay,
case when Holidays.HolidayDate is null then 0 else 1 end as IsHoliday,
coalesce(Holidays.Holiday, '') as HolidayName
from	DateList
left outer join Holidays on DateList.DateValue = Holidays.HolidayDate

7. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
...oops. You'll need to create and populate this table first:
Code:
CREATE TABLE [dbo].[SequentialNumbers](
[Number] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Number] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

;
with NumList (NumVal) as
(select 0 as NumVal
UNION ALL
select NumVal + 1
from NumList
where NumVal < 9)
insert into SequentialNumbers (Number)
select	Ones.NumVal + Tens.NumVal * 10 + Hundreds.NumVal * 100 + Thousands.NumVal * 1000
from	NumList Ones,
NumList Tens,
NumList Hundreds,
NumList Thousands
where not exists
(select	*
from	SequentialNumbers
where	Number = Ones.NumVal + Tens.NumVal * 10 + Hundreds.NumVal * 100 + Thousands.NumVal * 1000)

8. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
I like to include weekends to holidays table.
Because,
(1) It may be not permanently guaranteed weekends being holidays.
For example, some Japanese companies(e.g. Toyota) had been trying to 3 days holidays in a week
or to work on week ends and to make holidays in weekdays
for cost saving and/or averaging of use of electricity.
(2) Logics of holidays are separated into two parts "holidays" table and query logic,
if weekends were not included in holidays table.
(This might be more serious in maintenance phase.)

But, if you don't want to put weekends to "holidays" table,

I'm sorry my example was wrong in /* number of weekends between @startDate and @endDate */
So, please try blindman's example.
Last edited by tonkuma; 08-28-12 at 02:15. Reason: Spelling(electricity). Removed wrong sample code.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•