T-SQL gets the number of business days between two dates
I want to calculate the number of business days between two given dates. For example, if I want to calculate business days between 2013-01-10 and 2013-01-15, the result should be 3 business days (I do not take into account the last day in this interval, and I subtract Saturday and Sunday). I have the following code that works for most cases except one in my example.
SELECT (DATEDIFF(day, '2013-01-10', '2013-01-15'))
- (CASE WHEN DATENAME(weekday, '2013-01-10') = 'Sunday' THEN 1 ELSE 0 END)
- (CASE WHEN DATENAME(weekday, DATEADD(day, -1, '2013-01-15')) = 'Saturday' THEN 1 ELSE 0 END)
How can i do this? Do I have to go through all the days and check them? Or is there an easy way to do it.
source to share
Please, please use the calendar table. SQL Server knows nothing about national holidays, company events, natural disasters, etc. A calendar table is fairly easy to build, takes up very little space, and will be in memory if referenced enough.
Here is an example that creates a calendar table with 30 year dates (2000 → 2029), but only requires 200KB of disk space (136KB if you are using page compression). This is almost guaranteed to be less than the memory allocation required to handle some CTE or other set at runtime.
CREATE TABLE dbo.Calendar
(
dt DATE PRIMARY KEY, -- use SMALLDATETIME if < SQL Server 2008
IsWorkDay BIT
);
DECLARE @s DATE, @e DATE;
SELECT @s = '2000-01-01' , @e = '2029-12-31';
INSERT dbo.Calendar(dt, IsWorkDay)
SELECT DATEADD(DAY, n-1, '2000-01-01'), 1
FROM
(
SELECT TOP (DATEDIFF(DAY, @s, @e)+1) ROW_NUMBER()
OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
) AS x(n);
SET DATEFIRST 1;
-- weekends
UPDATE dbo.Calendar SET IsWorkDay = 0
WHERE DATEPART(WEEKDAY, dt) IN (6,7);
-- Christmas
UPDATE dbo.Calendar SET IsWorkDay = 0
WHERE MONTH(dt) = 12
AND DAY(dt) = 25
AND IsWorkDay = 1;
-- continue with other holidays, known company events, etc.
Now the query you want is pretty simple to write:
SELECT COUNT(*) FROM dbo.Calendar
WHERE dt >= '20130110'
AND dt < '20130115'
AND IsWorkDay = 1;
More information on calendar tables:
More information on generator sets without cycles:
http://www.sqlperformance.com/tag/date-ranges
Also, be careful with small things like relying on the English output DATENAME
. I've seen several apps get interrupted because some users had a different language setting, and if you are relying on WEEKDAY
, make sure you set the setting correctly DATEFIRST
...
source to share
For things like this, I tend to maintain a calendar table that also includes holidays, etc.
The script I have for this is as follows (note that I did not write it @, I forgot where I found it)
SET DATEFIRST 1
SET NOCOUNT ON
GO
--Create ISO week Function (thanks BOL)
CREATE FUNCTION ISOweek ( @DATE DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @ISOweek INT
SET @ISOweek = DATEPART(wk, @DATE) + 1 - DATEPART(wk, CAST(DATEPART(yy, @DATE) AS CHAR(4)) + '0104')
--Special cases: Jan 1-3 may belong to the previous year
IF ( @ISOweek = 0 )
SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy, @DATE) - 1 AS CHAR(4)) + '12' + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2))) + 1
--Special case: Dec 29-31 may belong to the next year
IF ( ( DATEPART(mm, @DATE) = 12 )
AND ( ( DATEPART(dd, @DATE) - DATEPART(dw, @DATE) ) >= 28 )
)
SET @ISOweek = 1
RETURN(@ISOweek)
END
GO
--END ISOweek
--CREATE Easter algorithm function
--Thanks to Rockmoose (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45689)
CREATE FUNCTION fnDLA_GetEasterdate ( @year INT )
RETURNS CHAR(8)
AS
BEGIN
-- Easter date algorithm of Delambre
DECLARE @A INT ,
@B INT ,
@C INT ,
@D INT ,
@E INT ,
@F INT ,
@G INT ,
@H INT ,
@I INT ,
@K INT ,
@L INT ,
@M INT ,
@O INT ,
@R INT
SET @A = @YEAR % 19
SET @B = @YEAR / 100
SET @C = @YEAR % 100
SET @D = @B / 4
SET @E = @B % 4
SET @F = ( @B + 8 ) / 25
SET @G = ( @B - @F + 1 ) / 3
SET @H = ( 19 * @A + @B - @D - @G + 15 ) % 30
SET @I = @C / 4
SET @K = @C % 4
SET @L = ( 32 + 2 * @E + 2 * @I - @H - @K ) % 7
SET @M = ( @A + 11 * @H + 22 * @L ) / 451
SET @O = 22 + @H + @L - 7 * @M
IF @O > 31
BEGIN
SET @R = @O - 31 + 400 + @YEAR * 10000
END
ELSE
BEGIN
SET @R = @O + 300 + @YEAR * 10000
END
RETURN @R
END
GO
--END fnDLA_GetEasterdate
--Create the table
CREATE TABLE MyDateTable
(
FullDate DATETIME NOT NULL
CONSTRAINT PK_FullDate PRIMARY KEY CLUSTERED ,
Period INT ,
ISOWeek INT ,
WorkingDay VARCHAR(1) CONSTRAINT DF_MyDateTable_WorkDay DEFAULT 'Y'
)
GO
--End table create
--Populate table with required dates
DECLARE @DateFrom DATETIME ,
@DateTo DATETIME ,
@Period INT
SET @DateFrom = CONVERT(DATETIME, '20000101')
--yyyymmdd (1st Jan 2000) amend as required
SET @DateTo = CONVERT(DATETIME, '20991231')
--yyyymmdd (31st Dec 2099) amend as required
WHILE @DateFrom <= @DateTo
BEGIN
SET @Period = CONVERT(INT, LEFT(CONVERT(VARCHAR(10), @DateFrom, 112), 6))
INSERT MyDateTable
( FullDate ,
Period ,
ISOWeek
)
SELECT @DateFrom ,
@Period ,
dbo.ISOweek(@DateFrom)
SET @DateFrom = DATEADD(dd, +1, @DateFrom)
END
GO
--End population
/* Start of WorkingDays UPDATE */
UPDATE MyDateTable
SET WorkingDay = 'B' --B = Bank Holiday
--------------------------------EASTER---------------------------------------------
WHERE FullDate = DATEADD(dd, -2, CONVERT(DATETIME, dbo.fnDLA_GetEasterdate(DATEPART(yy, FullDate)))) --Good Friday
OR FullDate = DATEADD(dd, +1, CONVERT(DATETIME, dbo.fnDLA_GetEasterdate(DATEPART(yy, FullDate))))
--Easter Monday
GO
UPDATE MyDateTable
SET WorkingDay = 'B'
--------------------------------NEW YEAR-------------------------------------------
WHERE FullDate IN ( SELECT MIN(FullDate)
FROM MyDateTable
WHERE DATEPART(mm, FullDate) = 1
AND DATEPART(dw, FullDate) NOT IN ( 6, 7 )
GROUP BY DATEPART(yy, FullDate) )
---------------------MAY BANK HOLIDAYS(Always Monday)------------------------------
OR FullDate IN ( SELECT MIN(FullDate)
FROM MyDateTable
WHERE DATEPART(mm, FullDate) = 5
AND DATEPART(dw, FullDate) = 1
GROUP BY DATEPART(yy, FullDate) )
OR FullDate IN ( SELECT MAX(FullDate)
FROM MyDateTable
WHERE DATEPART(mm, FullDate) = 5
AND DATEPART(dw, FullDate) = 1
GROUP BY DATEPART(yy, FullDate) )
--------------------AUGUST BANK HOLIDAY(Always Monday)------------------------------
OR FullDate IN ( SELECT MAX(FullDate)
FROM MyDateTable
WHERE DATEPART(mm, FullDate) = 8
AND DATEPART(dw, FullDate) = 1
GROUP BY DATEPART(yy, FullDate) )
--------------------XMAS(Move to next working day if on Sat/Sun)--------------------
OR FullDate IN ( SELECT CASE WHEN DATEPART(dw, FullDate) IN ( 6, 7 ) THEN DATEADD(dd, +2, FullDate)
ELSE FullDate
END
FROM MyDateTable
WHERE DATEPART(mm, FullDate) = 12
AND DATEPART(dd, FullDate) IN ( 25, 26 ) )
GO
---------------------------------------WEEKENDS--------------------------------------
UPDATE MyDateTable
SET WorkingDay = 'N'
WHERE DATEPART(dw, FullDate) IN ( 6, 7 )
GO
/* End of WorkingDays UPDATE */
--SELECT * FROM MyDateTable ORDER BY 1
DROP FUNCTION fnDLA_GetEasterdate
DROP FUNCTION ISOweek
--DROP TABLE MyDateTable
SET NOCOUNT OFF
Once you've created the table, it's easy to find the number of working days:
SELECT COUNT(FullDate) AS WorkingDays
FROM dbo.tbl_WorkingDays
WHERE WorkingDay = 'Y'
AND FullDate >= CONVERT(DATETIME, '10/01/2013', 103)
AND FullDate < CONVERT(DATETIME, '15/01/2013', 103)
Note that this script includes UK public holidays, I'm not sure which region you are in.
source to share
Here's a simple function that takes into account weekdays, not including Saturday and Sunday (not required when counting holidays):
CREATE FUNCTION dbo.udf_GetBusinessDays (
@START_DATE DATE,
@END_DATE DATE
)
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @NUMBER_OF_DAYS INT = 0;
DECLARE @DAY_COUNTER INT = 0;
DECLARE @BUSINESS_DAYS INT = 0;
DECLARE @CURRENT_DATE DATE;
DECLARE @DAYNAME NVARCHAR(9)
SET @NUMBER_OF_DAYS = DATEDIFF(DAY, @START_DATE, @END_DATE);
WHILE @DAY_COUNTER <= @NUMBER_OF_DAYS
BEGIN
SET @CURRENT_DATE = DATEADD(DAY, @DAY_COUNTER, @START_DATE)
SET @DAYNAME = DATENAME(WEEKDAY, @CURRENT_DATE)
SET @DAY_COUNTER += 1
IF @DAYNAME = N'Saturday' OR @DAYNAME = N'Sunday'
BEGIN
CONTINUE
END
ELSE
BEGIN
SET @BUSINESS_DAYS += 1
END
END
RETURN @BUSINESS_DAYS
END
GO
source to share
This is the method I usually use (when not using a calendar table):
DECLARE @T TABLE (Date1 DATE, Date2 DATE);
INSERT @T VALUES ('20130110', '20130115'), ('20120101', '20130101'), ('20120611', '20120701');
SELECT Date1, Date2, WorkingDays
FROM @T t
CROSS APPLY
( SELECT [WorkingDays] = COUNT(*)
FROM Master..spt_values s
WHERE s.Number BETWEEN 1 AND DATEDIFF(DAY, t.date1, t.Date2)
AND s.[Type] = 'P'
AND DATENAME(WEEKDAY, DATEADD(DAY, s.number, t.Date1)) NOT IN ('Saturday', 'Sunday')
) wd
If I have a table with holidays, you can add this too:
SELECT Date1, Date2, WorkingDays
FROM @T t
CROSS APPLY
( SELECT [WorkingDays] = COUNT(*)
FROM Master..spt_values s
WHERE s.Number BETWEEN 1 AND DATEDIFF(DAY, t.date1, t.Date2)
AND s.[Type] = 'P'
AND DATENAME(WEEKDAY, DATEADD(DAY, s.number, t.Date1)) NOT IN ('Saturday', 'Sunday')
AND NOT EXISTS
( SELECT 1
FROM HolidayTable ht
WHERE ht.Date = DATEADD(DAY, s.number, t.Date1)
)
) wd
The above will only work if your dates are within 2047 days of each other, if you are likely to be calculating large date ranges you can use this:
SELECT Date1, Date2, WorkingDays
FROM @T t
CROSS APPLY
( SELECT [WorkingDays] = COUNT(*)
FROM ( SELECT [Number] = ROW_NUMBER() OVER(ORDER BY s.number)
FROM Master..spt_values s
CROSS JOIN Master..spt_values s2
) s
WHERE s.Number BETWEEN 1 AND DATEDIFF(DAY, t.date1, t.Date2)
AND DATENAME(WEEKDAY, DATEADD(DAY, s.number, t.Date1)) NOT IN ('Saturday', 'Sunday')
) wd
source to share
I made my code in SQL SERVER 2008 (MS SQL). It works great for me. Hope this helps you.
DECLARE @COUNTS int,
@STARTDATE date,
@ENDDATE date
SET @STARTDATE ='01/21/2013' /*Start date in mm/dd/yyy */
SET @ENDDATE ='01/26/2013' /*End date in mm/dd/yyy */
SET @COUNTS=0
WHILE (@STARTDATE<=@ENDDATE)
BEGIN
/*Check for holidays*/
IF ( DATENAME(weekday,@STARTDATE)<>'Saturday' and DATENAME(weekday,@STARTDATE)<>'Sunday')
BEGIN
SET @COUNTS=@COUNTS+1
END
SET @STARTDATE=DATEADD(day,1,@STARTDATE)
END
/* Display the no of working days */
SELECT @COUNTS
source to share