In MS SQL, for getting Business Days between the two Dates or Working Days between the two Dates, it is always good to maintain a calender table. Though you can find all the week days excluding 'Saturday' and 'Sunday' by the query script as below:
DECLARE @FromDate DATETIME, @ToDate DATETIME
SET @FromDate = '2012-04-01'
SET @ToDate = '2015-03-31'
;WITH CTE
AS
(
SELECT CAST(@FromDate AS DATETIME) WorkingDate
,DATENAME(DW,@FromDate) DayName
UNION ALL
SELECT WorkingDate +
1 AS WorkingDate
,DATENAME(DW,WorkingDate + 1) DayName
FROM CTE
WHERE WorkingDate <=
@ToDate
)
SELECT *
FROM CTE
WHERE DayName NOT IN ('Saturday','Sunday')
OPTION (MAXRECURSION 0)
But when you see the actual calender of an Organization, it includes National Holidays, Regional Holidays, some Festival Holidays etc.. So maintaining a calender table is needed. Here is an example, how we can maintain a Calender table:
----- Creating a
Calender Table :
CREATE TABLE CalenderTable
(
DATE DATETIME
,DayName VARCHAR(10)
,[Month] INT
,[Year] INT
,IsBusinessDay BIT
,Description VARCHAR(50)
)
--- Inserting Dates
excluding 'Saturday' and 'Sunday':
DECLARE @FromDate DATETIME, @ToDate DATETIME
SET @FromDate = '2012-04-01'
SET @ToDate = '2015-03-31'
;WITH CTE
AS
(
SELECT CAST(@FromDate AS DATETIME) WorkingDate
,DATENAME(DW,@FromDate) DayName
,MONTH(@FromDate) Months,YEAR(@FromDate) Years
UNION ALL
SELECT WorkingDate +
1 AS WorkingDate
,DATENAME(DW,WorkingDate + 1) DayName
,MONTH(WorkingDate + 1) Months,YEAR(WorkingDate + 1) Years
FROM CTE
WHERE WorkingDate <=
@ToDate
)
INSERT INTO CalenderTable
SELECT CTE.* ,1,'Business Day'
FROM CTE
WHERE DayName NOT IN ('Saturday','Sunday')
OPTION (MAXRECURSION 0)
---Inserting
Dates falling on 'Saturday' or 'Sunday':
;WITH CTE
AS
(
SELECT CAST(@FromDate AS DATETIME) WorkingDate
,DATENAME(DW,@FromDate) DayName
,MONTH(@FromDate) Months,YEAR(@FromDate) Years
UNION ALL
SELECT WorkingDate +
1 AS WorkingDate
,DATENAME(DW,WorkingDate + 1) DayName
,MONTH(WorkingDate + 1) Months,YEAR(WorkingDate + 1) Years
FROM CTE
WHERE WorkingDate <=
@ToDate
)
INSERT INTO CalenderTable
SELECT CTE.* ,0,'WEEK OFF'
FROM CTE
WHERE DayName IN ('Saturday','Sunday')
OPTION (MAXRECURSION 0)
--- Updating
other Dates which are Declared Holidays:
UPDATE CalenderTable
SET
IsBusinessDay = 0
,Description = 'Independence Day'
WHERE DATE = '2012-08-15'
UPDATE CalenderTable
SET
IsBusinessDay = 0
,Description = 'Christmas Day'
WHERE DATE = '2012-12-25'
UPDATE CalenderTable
SET
IsBusinessDay = 0
,Description = 'Republic Day'
WHERE DATE = '2013-01-26'
UPDATE CalenderTable
SET
IsBusinessDay = 0
,Description = 'Mahatma Gandhi Birth
Anniversary'
WHERE DATE = '2013-01-26'
Now we can fire any type of query from the Calender Table.
Let's say we want to get all the working days between '2012-08-10' and '2012-08-20', then we will have :
SELECT DATE
FROM
CalenderTable
WHERE DATE BETWEEN '2012-08-10' AND '2012-08-20'
AND
IsBusinessDay = 1
--- Output is :
2012-08-10 00:00:00.000
2012-08-13 00:00:00.000
2012-08-14 00:00:00.000
2012-08-16 00:00:00.000
2012-08-17 00:00:00.000
2012-08-20 00:00:00.000
Reference: Govind Badkur(
http://govindbadkur.blogspot.com)
