Update column value if Saturday ,Sunday and Monday Day value is null

Question

Below is my data,i want when employee is absent means that Day value is null Before Sunday and After sunday ,then update column value 2 ,and it also check table #MonthDate ,that day is marked 'H' in Holiday ,then condition will apply else not.

Create table #EmpMaster (EnrollNumber int, empName varchar(50))  
Create table #MonthDate (Day varchar(50),Date date,Holiday varchar(50))  


  
 Create table #tbl_Emp_Register  ( EnrollNumbeR int,Date date,Day varchar(5),TimeIN time(7),TimeOut time(7),Duty int)  
  
                     Insert into #EmpMaster values (10001,'ALi')  
                     Insert into #EmpMaster values (10002,'Hussain')  
                     Insert into #MonthDate values ('Wednesday','2022-11-01',null)   
                     Insert into #MonthDate values ('Thursday','2022-11-02',null)   
                     Insert into #MonthDate values ('Friday','2022-11-03',null)   
                     Insert into #MonthDate values ('saturday','2022-11-04',null)   
                     Insert into #MonthDate values ('Sunday','2022-11-05','H')   
                     Insert into #MonthDate values ('Monday','2022-11-06',null) 
                  

                          
                Insert into #tbl_Emp_Register values (10001,'2022-11-01','Wed' ,'08:04:00','17:45:00',1)  
              
                Insert into #tbl_Emp_Register values (10001,'2022-11-02' ,'Thu','08:04:00','17:45:00',1)  
     
                Insert into #tbl_Emp_Register values (10001,'2022-11-03','Fri', '11:45:00','18:45:00',1)  
               
				Insert into #tbl_Emp_Register values (10001,'2022-11-04','Sat',null,null,null)  

				Insert into #tbl_Emp_Register values (10001,'2022-11-05','Sun', null,null,null)  

				Insert into #tbl_Emp_Register values (10001,'2022-11-06','Mon', null,null,null)  


				Insert into #tbl_Emp_Register values (10002,'2022-11-03','Fri', '11:45:00','18:45:00',1)  

				Insert into #tbl_Emp_Register values (10002,'2022-11-04','Sat',null,null,null)  

				Insert into #tbl_Emp_Register values (10002,'2022-11-05','Sun', null,null,null)  

				Insert into #tbl_Emp_Register values (10002,'2022-11-06','Mon', '09:45:00','17:45:00',1)  

my expected out is below

Answer 1

Hi @Analyst_SQL Try this:

;WITH CTE1 AS
(
 SELECT T.*,M.Holiday
       ,LAG(Duty)OVER(PARTITION BY EnrollNumber ORDER BY T.Date) AS Previous_Duty
	   ,LEAD(Duty)OVER(PARTITION BY EnrollNumber ORDER BY T.Date) AS Next_Duty
 FROM #tbl_Emp_Register T LEFT JOIN #MonthDate M ON T.Date=M.Date
),CTE2 AS
(
 SELECT EnrollNumbeR,[Date],[Day],TimeIN,TimeOut,Duty
       ,CASE WHEN Holiday='H' AND Previous_Duty IS NULL AND Next_Duty IS NULL THEN 2 
	         WHEN Holiday='H' AND (Previous_Duty = 1 OR Next_Duty = 1) THEN 1
	         END AS New_Duty
 FROM CTE1
),CTE3 AS
(
 SELECT EnrollNumbeR,[Date],[Day],TimeIN,TimeOut,Duty
       ,CASE WHEN Duty IS NULL AND LAG(New_Duty)OVER(PARTITION BY EnrollNumber ORDER BY Date)=2 THEN 2
	         WHEN Duty IS NULL AND LEAD(New_Duty)OVER(PARTITION BY EnrollNumber ORDER BY Date)=2 THEN 2
	 	 	 ELSE New_Duty END AS New_Duty
FROM CTE2
)
UPDATE CTE3
SET Duty=New_Duty WHERE New_Duty IS NOT NULL

Best regards,

Cosmog Hong