Non Working Days Between the Two Dates

    In MS SQL, for getting Working Days or Non Working Days between the two Dates, it is always good to maintain a calender table. Though you can find all the Dates falling on 'Saturday' or '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 non 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 = 0



--- Output is :

2012-08-11 00:00:00.000

2012-08-12 00:00:00.000

2012-08-15 00:00:00.000

2012-08-18 00:00:00.000

2012-08-19 00:00:00.000

Reference: Govind Badkur(http://govindbadkur.blogspot.com)