GSquared (6/9/2008)
For accurate counts of weekdays, I HIGHLY recommend a calendar table. That way, you can include holidays, etc.
create table Calendar (
Date datetime primary key,
constraint CK_Date_NoTime
check (Date = cast(cast(cast(date as float) as int) as datetime)),
Workday bit not null,
Year as datepart(year, date),
Month as datepart(month, date),
Day as datepart(day, date),
WeekDay as datepart(weekday, date))
go
insert into dbo.Calendar (Date, Workday)
select dateadd(day, number, '1/1/2000'),
case
when datepart(weekday, dateadd(day, number, '1/1/2000')) between 2 and 6 then 1
else 0
end
from dbo.numbers -- A table of 10-thousand numbers
go
create index IDX_Calendar_MonthDay on dbo.Calendar(month, day)
go
update dbo.Calendar
set workday = 0
where month = 7 and day = 4
or ... -- fill in other holidays here
go
select count(*)
from dbo.Calendar
where workday = 1
and date between '5/20/2008' and '6/9/2008'
With a table like that, you can easily have SQL do things like calculate the 1st Monday in September, or the 4th Thursday in November, record these things as holidays, and calculate workdays between dates.
;with Thanksgiving (Row, Date, Workday) as
(select row_number() over (order by date), date, workday
from dbo.Calendar
where Month = 11
and WeekDay = 5)
update Thanksgiving
set workday = 0
where row = 4
You can also add more indexes to it, if you so desire.
Using a 10-thousand day calendar starting from 1 Jan 2000, will give you a table that goes into 2027, which should be enough to start with. As needed, add more, or create a job that runs on the first day of each year and adds another year worth of days (if you do that, make sure it adds enough to account for leap years).
Using the code above I came up with the code below for adding company holidays:
How do I do it without using a cursor?
--http://www.sqlservercentral.com/Forums/Topic513315-149-1.aspx#bm513318
IF OBJECT_ID('Calendar', 'U') IS NOT NULL
DROP TABLE Calendar
create table Calendar (
Date datetime primary key,
constraint CK_Date_NoTime
check (Date = cast(cast(cast(date as float) as int) as datetime)),
Workday bit not null,
Year as datepart(year, date),
Month as datepart(month, date),
Day as datepart(day, date),
WeekDay as datepart(weekday, date),
LongDay as datename(weekday, date),
DayType varchar(50))
go
--create table Numbers (
--Num_ID int)
DECLARE @Numbers TABLE
(
Num_ID INT
)
declare @number as int
set @number =1
while @number <10001
begin
insert into @Numbers (num_id) values (@number)
set @number=@number+1
end
insert into dbo.Calendar (Date, Workday,DayType)
select dateadd(day, num_id, '12/31/1999'),
case
when datepart(weekday, dateadd(day, num_id, '12/31/1999')) between 2 and 6 then 1
else 0
end,
case
when datepart(weekday, dateadd(day, num_id, '12/31/1999')) between 2 and 6 then 'Work'
else 'Weekend'
end
from @Numbers -- A table of 10-thousand numbers
go
create index IDX_Calendar_MonthDay on dbo.Calendar(month, day)
go
update dbo.Calendar
set workday = 0, DayType='New Years Day'
where month = 1 and day = 1
go
update dbo.Calendar
set workday = 0, DayType='Independance Day'
where month = 7 and day = 4
go
update dbo.Calendar
set workday = 0, DayType='Christmas Day'
where month = 12 and day = 25
go
DECLARE @CalendarYear TABLE
(
[Year] INT
)
DECLARE @year int
INSERT INTO @CalendarYear
select distinct year from calendar order by year
DECLARE cYear CURSOR
FOR SELECT year FROM @CalendarYear
OPEN cYear
fetch cYear into @year
while @@fetch_status=0
begin
update dbo.Calendar set workday = 0, DayType='Memorial Day' where date=(select max(date) from calendar where month =5 and year=@year and weekday=2)
update dbo.Calendar set workday = 0, DayType='Labor Day' where date=(select min(date) from calendar where month =9 and year=@year and weekday=2)
update dbo.Calendar set workday = 0, DayType='Thanksgiving Day' where date=(select max(date)-1 from calendar where month =11 and year=@year and weekday=6)
update dbo.Calendar set workday = 0, DayType='Day after Thanksgiving Day' where date=(select max(date) from calendar where month =11 and year=@year and weekday=6)
fetch cYear into @year
end
close cYear
/*
1st JanNew Year's Day
Last Monday in MayMemorial Day
4th JulIndependence Day
First Monday in SeptemberLabor Day
Fourth Thursday in NovemberThanksgiving Day
25th DecChristmas Day
*/