Consecutive Dates with Exclusion

  
 
  



 CREATE TABLE [dbo].[PaidTimeOff](
[StaffCode] [varchar](50) NULL,
[VacationDate] [datetime] NULL
) ON [PRIMARY]
GO


INSERT INTO PaidTimeOFF (StaffCode, VacationDate) VALUES ('L.Johnson',
'2018-03-21 00:00:00')
INSERT INTO PaidTimeOFF (StaffCode, VacationDate) VALUES ('L.Johnson',
'2018-05-07 00:00:00')
INSERT INTO PaidTimeOFF (StaffCode, VacationDate) VALUES ('L.Johnson',
'2018-05-08 00:00:00')
INSERT INTO PaidTimeOFF (StaffCode, VacationDate) VALUES ('L.Johnson',
'2018-05-09 00:00:00')
INSERT INTO PaidTimeOFF (StaffCode, VacationDate) VALUES ('L.Johnson',  '2018-06-29 00:00:00')
INSERT INTO PaidTimeOFF (StaffCode, VacationDate) VALUES ('L.Johnson',  '2018-07-02 00:00:00')
INSERT INTO PaidTimeOFF (StaffCode, VacationDate) VALUES ('L.Johnson',  '2018-07-03 00:00:00')
INSERT INTO PaidTimeOFF (StaffCode, VacationDate) VALUES ('L.Johnson',  '2018-07-05 00:00:00')
INSERT INTO PaidTimeOFF (StaffCode, VacationDate) VALUES ('L.Johnson',  '2018-07-06 00:00:00')
  
  

declare @startdate date
declare @enddate date  
 
Select @startdate = min(Vacationdate),@enddate = max(Vacationdate) from [PaidTimeOff] 
 
--****  create a Number table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
 
 --  
,myStaffCodeDate as
(
select  StaffCode,dateadd(day,n-1,@startdate) dt from Nums 
Cross join (Select Distinct StaffCode From  [PaidTimeOff] ) a
where dateadd(day,n-1,@startdate)<=@enddate)       
 
,mycte as (
 select m.StaffCode, m.dt,
 Case when datepart(weekday,m.dt) in (1,7) 
 or (datepart(month ,m.dt)=7 and datepart(day,m.dt)=4) 
 then m.dt else p.VacationDate  end newDt
 from myStaffCodeDate m Left join [PaidTimeOff] p
 on m.StaffCode=p.StaffCode and m.dt=p.VacationDate)

 ,mycte2 as (select StaffCode,newdt, dt, DATEADD(dd, - ROW_NUMBER() OVER (ORDER BY  newDt),  newDt) grp
 from mycte

 )

 Select StaffCode, min(newdt) firstDay, max(newdt) lastDay,
  count(*) TotalDaysOff 
  from mycte2
  where  datepart(weekday, dt) Not in (1,7) 
 and  not  (datepart(month , dt)=7 and datepart(day, dt)=4) 
 and newdt is not null
 group by StaffCode, grp

 drop table [PaidTimeOff]



 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0976edae-dc2d-486d-baa3-945fad0ea577/consecutive-dates?forum=transactsql

Advertisements