Update Column on Condition before and after.

Question

I want ,when employee Duty is marked with '0' before 'Sun' means ('Sat') and after 'Sun' means ('Mon') in table #tbl_Emp_Register.Then update query required,which will check and marked that day with '2'.as i attached image for expected output.

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),Duty int)  
  
                     Insert into #EmpMaster values (10001,'ALi')  
                 
                     Insert into #MonthDate values ('Wed','2022-11-01',null)   
                     Insert into #MonthDate values ('Thu','2022-11-02',null)   
                     Insert into #MonthDate values ('Fri','2022-11-03',null)   
                     Insert into #MonthDate values ('sat','2022-11-04',null)   
                     Insert into #MonthDate values ('Sun','2022-11-05',null)   
                     Insert into #MonthDate values ('Mon','2022-11-06',null) 
					 Insert into #MonthDate values ('Tue','2022-11-07',null)  
                     Insert into #MonthDate values ('Wed','2022-11-08',null)   
                     Insert into #MonthDate values ('Thu','2022-11-09',null)   
                     Insert into #MonthDate values ('Fri','2022-11-10',null)   
                     Insert into #MonthDate values ('Sat','2022-11-11',null)   
                     Insert into #MonthDate values ('Sun','2022-11-12',null)   
                     Insert into #MonthDate values ('Mon','2022-11-13',null) 

                          
                Insert into #tbl_Emp_Register values (10001,'2022-11-01','Wed' ,1)  
              
                Insert into #tbl_Emp_Register values (10001,'2022-11-02' ,'Thu',1)  
     
                Insert into #tbl_Emp_Register values (10001,'2022-11-03','Fri',1)  
               
				Insert into #tbl_Emp_Register values (10001,'2022-11-04','Sat',0)  

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

				Insert into #tbl_Emp_Register values (10001,'2022-11-06','Mon',0)  
			
			    Insert into #tbl_Emp_Register values (10001,'2022-11-07','Tue' ,1)  
				
				Insert into #tbl_Emp_Register values (10001,'2022-11-08','Wed' ,1)  
              
                Insert into #tbl_Emp_Register values (10001,'2022-11-09' ,'Thu',1)  
     
                Insert into #tbl_Emp_Register values (10001,'2022-11-10','Fri',1)  
               
				Insert into #tbl_Emp_Register values (10001,'2022-11-11','Sat',1)  

				Insert into #tbl_Emp_Register values (10001,'2022-11-12','Sun',1)  

				Insert into #tbl_Emp_Register values (10001,'2022-11-13','Mon',0)  

Answer 1

Check a long variant:

update t3 set Duty = 2
from #tbl_Emp_Register t1
left join #tbl_Emp_Register t2 on t2.EnrollNumbeR = t1.EnrollNumbeR and datediff(day, t1.Date, t2.Date) = 1
left join #tbl_Emp_Register t3 on t3.EnrollNumbeR = t2.EnrollNumbeR and datediff(day, t2.Date, t3.Date) = 1
left join #tbl_Emp_Register t4 on t4.EnrollNumbeR = t3.EnrollNumbeR and datediff(day, t3.Date, t4.Date) = 1
left join #tbl_Emp_Register t5 on t5.EnrollNumbeR = t4.EnrollNumbeR and datediff(day, t4.Date, t5.Date) = 1
where 
    t3.Day = 'Sat' and t3.Duty = 0 and t4.Duty = 0 and t5.Duty = 0
    or                                                                    
    t3.Day = 'Sun' and t2.Duty = 0 and t3.Duty = 0 and t4.Duty = 0
    or                                                                    
    t3.Day = 'Mon' and t1.Duty = 0 and t2.Duty = 0 and t3.Duty = 0