If you want to take into account holidays or other non-working days, then best approach would be to create an auxiliary calendar table. Then you could run queries against this table, not just to calculate the number of work days, but you could do much more.
For example, you could easily count number of Mondays between two dates, get the last day in each month of this year, count number of Fridays between two dates, etc.
Calendar table is a simple table with pre-populated dates. You need to create and populate this table only once, then use it whenever needed. Not all columns are required. Include only those which you will need.
CREATE TABLE Calendar
(
Date smalldatetime NOT null PRIMARY KEY clustered,
IsWeekday bit,
IsHoliday bit,
TheYear smallint,
TheMonth tinyint,
TheDay tinyint,
TheQuarter tinyint,
TheWeek tinyint,
TheWeekday tinyint
)
GO
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Fill the calendar table with dates from a range which will suffice you.
Fill other columns in the calendar table. This query does not mark holidays because they are different in each country. You will have to set them yourself.
UPDATE Calendar SET
IsWeekday = CASE WHEN DATENAME(dw, Date) IN ('Saturday', 'Sunday') THEN 0 ELSE 1 END,
IsHoliday = 0,
TheYear = YEAR(Date),
TheMonth = MONTH(Date),
TheDay = DAY(Date),
TheQuarter = CASE
WHEN MONTH(Date) <= 3 THEN 1
WHEN MONTH(Date) <= 6 THEN 2
WHEN MONTH(Date) <= 9 THEN 3
ELSE 4 END,
TheWeek = DATEPART(wk, Date),
TheWeekday = DATEPART(dw, Date)
The calendar table is ready. You can now calculate the number of business days between two dates with this query:
DECLARE @startDate smalldatetime, @endDate smalldatetime
SELECT @startDate = '2010-05-10', @endDate = '2010-12-25';
SELECT COUNT(*)
FROM Calendar
WHERE Date >= @startDate AND Date <= @endDate
AND IsWeekday = 1 AND IsHoliday = 0
Or you can even easily list all those dates:
SELECT Date
FROM Calendar
WHERE Date >= @startDate AND Date <= @endDate
AND IsWeekday = 1 AND IsHoliday = 0
