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

Answer 1

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