skip holiday & weekly off from two date

Question

i am using sql server 2008 R2 , i am creating application leave form. when user apply leave i.e start date & end date then between two dates , all dates should skip if they fall in holiday & weekly off

CREATE TABLE #HolidayMaster
(
    [HolidayID] [int] IDENTITY(1,1) NOT NULL,
    [HolidayDescription] [nvarchar](50) NULL,
    [HolidayDate] [date] NULL,
) 

INSERT INTO #HolidayMaster 
select 'New Year', '2016-01-01'
union
select 'National Developer Day', '2016-01-05'


CREATE TABLE #ShiftMaster
(
    [ShiftID] [int] IDENTITY(1,1) NOT NULL,
    [Sunday] [float] NULL,
    [Monday] [float] NULL,
    [Tuesday] [float] NULL,
    [Wednesday] [float] NULL,
    [Thursday] [float] NULL,
    [Friday] [float] NULL,
    [Saturday] [float] NULL
) 

INSERT INTO #ShiftMaster 
select 0,1,1,1,1,1,0


select *,DATENAME (dw,[HolidayDate]) as [DayName] from #HolidayMaster
select * from #ShiftMaster

drop table #HolidayMaster
drop table #ShiftMaster

Declare @LeaveStartDate date = '2013-01-01'
Declare @LeaveEndDate date = '2013-01-06'

--expected out put

DName       Date        Desc    
Friday      2016-01-01  Holiday
Saturday    2016-01-02  WeeklyOff
Sunday      2016-01-03  WeeklyOff
Monday      2016-01-04  Working
Tuesday     2016-01-05  Holiday
Wednesday   2016-01-06  Working

Pரதீப் · Answer 1 · 2016-06-29T06:38:49.907

You need a calendar table. I have used tally table to generate dates.

Declare @LeaveStartDate date = '2016-01-01'
Declare @LeaveEndDate date = '2016-01-06'

;WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
     Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
     Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
     Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
     Nbrs  ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ),
     calendar(dates) as 
     (
SELECT Dateadd(dd, n - 1, @LeaveStartDate) AS Date
FROM   (SELECT Row_number()OVER (ORDER BY n)
        FROM   Nbrs) D ( n )
WHERE  n <= Datediff(day, @LeaveStartDate, @LeaveEndDate)+ 1
)
SELECT DName =d.day_name,
       Date = c.dates,
       CASE
         WHEN HolidayDate IS NOT NULL THEN 'Holiday'
         WHEN leav_iden = 0 THEN 'WeeklyOff'
         WHEN HolidayDate IS NOT NULL
              AND leav_iden = 0 THEN 'Holiday/WeeklyOff'
         ELSE 'Working'
       END AS [Desc]
FROM   calendar c
       JOIN (SELECT *
             FROM   #ShiftMaster
                    CROSS apply (VALUES ([Sunday],'Sunday'),
                                        ([Monday],'Monday'),
                                        ([Tuesday],'Tuesday'),
                                        ([Wednesday],'Wednesday'),
                                        ([Thursday],'Thursday'),
                                        ([Friday],'Friday'),
                                        ([Saturday],'Saturday') ) cs(leav_iden, day_name)) d
         ON Datename(WEEKDAY, c.dates) = d.day_name
       LEFT JOIN #HolidayMaster h
              ON h.HolidayDate = c.dates

Result :

DName       Date        Desc
-----       ----------  -------------
Sunday      2016-01-03  WeeklyOff
Monday      2016-01-04  Working
Tuesday     2016-01-05  Holiday
Wednesday   2016-01-06  Working
Friday      2016-01-01  Holiday
Saturday    2016-01-02  WeeklyOff

Jatin Patel · Answer 2 · 2016-06-29T09:44:08.617

try this,

DECLARE @LeaveStartDate DATETIME = '2016-01-01'
DECLARE @LeaveEndDate DATETIME = '2016-01-06'

;WITH CTE
AS ( SELECT @LeaveStartDate AS LeaveDate
    UNION ALL
    SELECT LeaveDate + 1 
    FROM CTE
    WHERE LeaveDate < @LeaveEndDate
)

SELECT * ,DATENAME(WEEKDAY, c.LeaveDate)
FROM CTE c
LEFT JOIN #HolidayMaster h ON c.LeaveDate = h.HolidayDate
WHERE h.HolidayDate IS NULL 
    AND EXISTS (    SELECT 1 
                    FROM #ShiftMaster s 
                    WHERE (DATENAME(WEEKDAY, c.LeaveDate) = 'Sunday' AND s.Sunday = 1)
                        OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Monday' AND s.Monday = 1)
                        OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Tuesday' AND s.Tuesday = 1)
                        OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Wednesday' AND s.Wednesday = 1)
                        OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Thursday' AND s.Thursday = 1)
                        OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Friday' AND s.Friday = 1)
                        OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Saturday' AND s.Saturday = 1)
                )

If you are looking to get all dates with status try this,

DECLARE @LeaveStartDate DATETIME = '2016-01-01'
DECLARE @LeaveEndDate DATETIME = '2016-01-06'

;WITH CTE
AS ( SELECT @LeaveStartDate AS LeaveDate
    UNION ALL
    SELECT LeaveDate + 1 
    FROM CTE
    WHERE LeaveDate < @LeaveEndDate
)

SELECT 
        c.LeaveDate,
        DATENAME(WEEKDAY, c.LeaveDate) AS [DayName],
        CASE WHEN   (DATENAME(WEEKDAY, c.LeaveDate) = 'Sunday' AND s.Sunday = 0)
                    OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Monday' AND s.Monday = 0)
                    OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Tuesday' AND s.Tuesday = 0)
                    OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Wednesday' AND s.Wednesday = 0)
                    OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Thursday' AND s.Thursday = 0)
                    OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Friday' AND s.Friday = 0)
                    OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Saturday' AND s.Saturday = 0)
                THEN 'WeeklyOff'
            WHEN h.HolidayDate IS NOT NULL 
                THEN 'Holiday'
            ELSE 'Working'
        END AS [Status]
FROM CTE c
CROSS JOIN #ShiftMaster s 
LEFT JOIN #HolidayMaster h ON c.LeaveDate = h.HolidayDate

you bring hard coded Sunday & Saturday , i need dynamic. can you bring day name from shiftmaster daysname = 0 — Naweez, Jun 29 '16 at 07:23
@Naweez, I have edited the query to check it from ShiftMaster — Jatin Patel, Jun 29 '16 at 07:39
@Naweez, added the alternate solution to get all dates with day status. — Jatin Patel, Jun 29 '16 at 09:45
can add one more cte for shiftmaster like nazark did above so so code will be simplified. — Naweez, Jun 29 '16 at 11:20

score 0 · Answer 3 · edited May 23 '17 at 11:44

First thing you need is to generate the dates based on an interval. A nice an quick way is shown here. Then, you can LEFT JOIN with your #HolidayMaster table to check if a date is a holiday or not.

The code should look like this:

Declare @LeaveStartDate date = '2016-01-01'
Declare @LeaveEndDate date = '2016-01-06'

;WITH Dates_CTE AS (
    SELECT  TOP (DATEDIFF(DAY, @LeaveStartDate, @LeaveEndDate) + 1)
            Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @LeaveStartDate)
    FROM    sys.all_objects a
            CROSS JOIN sys.all_objects b
)
SELECT DATENAME (dw,[Date]) as [DayName],
    [Date],
    (CASE   WHEN DATEPART(dw, [Date]) IN (1, 7) THEN 'WeeklyOff'
            WHEN HM.HolidayID IS NOT NULL THEN 'Holiday'    -- HM.Description can be used here to actually display holiday name
            ELSE 'Working' END) [Desc]
FROM Dates_CTE D
    LEFT JOIN #HolidayMaster HM ON HM.HolidayDate = D.[Date]

Usually LEFT JOIN .. IS NULL check is slower than NOT EXISTS, so an an alternative is to remove LEFT JOIN and check for existence, but I find this form more readable.

you have not consider shift master – Naweez Jun 29 '16 at 08:34 — Naweez, Jun 29 '16 at 08:34

score 0 · Answer 4 · answered Jun 29 '16 at 06:04

Recursive CTE to generate dates between the date range, left join on HolidayMaster and left join on ShiftMaster (I changed the ShiftMaster to have day names to join on), then select the proper description depending on the values:

CREATE TABLE #HolidayMaster
(
    [HolidayID] [int] IDENTITY(1,1) NOT NULL,
    [HolidayDescription] [nvarchar](50) NULL,
    [HolidayDate] [date] NULL,
) 

INSERT INTO #HolidayMaster 
select 'New Year', '2016-01-01'
union
select 'National Developer Day', '2016-01-05'


CREATE TABLE #ShiftMaster
(
    [ShiftID] [int] IDENTITY(1,1) NOT NULL,
    [Day] nvarchar(20) NULL,
    IsHoliday bit NULL
) 

INSERT INTO #ShiftMaster 
values ('Sunday', 0), ('Monday', 1), ('Tuesday', 1), ('Wednesday',1), ('Thursday', 1), ('Friday', 1), ('Saturday',0)


select *,DATENAME (dw,[HolidayDate]) as [DayName] from #HolidayMaster
select * from #ShiftMaster

Declare @LeaveStartDate date = '2016-01-01'
Declare @LeaveEndDate date = '2016-01-06'

;WITH Dates AS (
        SELECT
         [Date] = @LeaveStartDate
        UNION ALL SELECT
         [Date] = DATEADD(DAY, 1, [Date])
        FROM
         Dates
        WHERE
         Date < @LeaveEndDate
) SELECT
DATENAME(dw, [Date])
 , [Date]
 , CASE WHEN hm.HolidayDescription IS NULL THEN 
      CASE WHEN sm.IsHoliday = 1 THEN 'Working' ELSE 'Weekly Off' END
      ELSE 'Holiday' END AS Description 
FROM
 Dates
 left join #HolidayMaster hm on hm.HolidayDate = [Date]
 left join #ShiftMaster sm on sm.Day = DATENAME(dw, [Date])


drop table #HolidayMaster
drop table #ShiftMaster

you change my shiftmaster table , which i don't want – Naweez Jun 29 '16 at 08:36 — Naweez, Jun 29 '16 at 08:36

score 0 · Accepted Answer · answered Jun 29 '16 at 06:42

try

Declare @LeaveStartDate date = '2016-01-01'
Declare @LeaveEndDate date = '2016-01-06'


    ;with dts(dt) as (select @LeaveStartDate 
    union all 
    select dateadd(day,1,dt) from dts where dt<@LeaveEndDate),

        shifmstr as (
        select shiftID,'Sunday' as dname,[sunday] tp from #ShiftMaster union all
        select shiftID,'Monday' ,Monday from #ShiftMaster union all
        select shiftID,'Tuesday' ,Tuesday from #ShiftMaster union all
        select shiftID,'Wednesday',Wednesday from #ShiftMaster union all
        select shiftID,'Thursday',Thursday from #ShiftMaster union all
        select shiftID,'Friday',Friday from #ShiftMaster union all
        select shiftID,'Saturday',Saturday from #ShiftMaster)

    select datename(dw,dt) DName,Dt, 
    case when c.HolidayDate is not null then 'Holiday'
     when tp=1 then 'Working' else 'WeeklyOff'    end descr
    from dts a join shifmstr b on 
    datename(dw,a.dt)=b.dname left join 
    #HolidayMaster c on a.dt=c.HolidayDate

skip holiday & weekly off from two date

5 Answers5