Update Column on Condition before and after Holiday

Analyst_SQL 3,576 Reputation points
2023-07-05T08:20:01.96+00:00

I want ,I marked Holiday in #MonthDate column Holiday with 'H',

when employee Duty is marked with '0' before Holidays('H') and after 'Holidays('H') in table #MonthDate.Then update query required, which will check and marked that day with '2'.as i attached image for expected output.

Second condition

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 #MonthDate values ('Tue','2022-11-14',null)  
                     Insert into #MonthDate values ('Wed','2022-11-15',null)   
                     Insert into #MonthDate values ('Thu','2022-11-16','H')   
                     Insert into #MonthDate values ('Fri','2022-11-17','H')   
                     Insert into #MonthDate values ('Sat','2022-11-18','H')   
                     Insert into #MonthDate values ('Sun','2022-11-19','H')   
                     Insert into #MonthDate values ('Mon','2022-11-20',null) 
					  Insert into #MonthDate values ('Tue','2022-11-21',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)  

			    Insert into #tbl_Emp_Register values (10001,'2022-11-14','Tue' ,1)  
				
				Insert into #tbl_Emp_Register values (10001,'2022-11-15','Wed' ,0)  
              
                Insert into #tbl_Emp_Register values (10001,'2022-11-16' ,'Thu',0)  
     
                Insert into #tbl_Emp_Register values (10001,'2022-11-17','Fri',0)  
               
				Insert into #tbl_Emp_Register values (10001,'2022-11-18','Sat',0)  

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

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

				 Insert into #tbl_Emp_Register values (10001,'2022-11-21','Tue' ,1)  


Output

User's image

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,476 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,993 questions
{count} votes

Accepted answer
  1. LiHongMSFT-4306 30,671 Reputation points
    2023-07-07T03:12:47+00:00

    Hi @Analyst_SQL

    How about this:

    ;WITH CTE1 AS
    (
    SELECT R.*,M.Holiday
          ,LAG(Duty)OVER(PARTITION BY R.EnrollNumbeR ORDER BY R.Date) AS Prev_Duty1
    	  ,LAG(Duty,2)OVER(PARTITION BY R.EnrollNumbeR ORDER BY R.Date) AS Prev_Duty2
    	  ,LEAD(Duty)OVER(PARTITION BY R.EnrollNumbeR ORDER BY R.Date) AS Next_Duty1
    	  ,LEAD(Duty,2)OVER(PARTITION BY R.EnrollNumbeR ORDER BY R.Date) AS Next_Duty2
    	  ,LAG(Holiday)OVER(PARTITION BY R.EnrollNumbeR ORDER BY R.Date) AS Prev_Holiday
    	  ,LEAD(Holiday)OVER(PARTITION BY R.EnrollNumbeR ORDER BY R.Date) AS Next_Holiday
    FROM #EmpMaster E 
         JOIN #tbl_Emp_Register R ON E.EnrollNumber=R.EnrollNumbeR
         LEFT JOIN #MonthDate M ON R.Date = M.Date
    ),CTE2 AS
    (
    SELECT *,CASE WHEN Holiday='H' 
    	            OR (Day='Sat' AND Duty = 0 AND Next_Duty1 = 0 AND Next_Duty2 = 0)
    				OR (Day='Sun' AND Duty = 0 AND Next_Duty1 = 0 AND Prev_Duty1 = 0)
    				OR (Day='Mon' AND Duty = 0 AND Prev_Duty1 = 0 AND Prev_Duty2 = 0)
    				OR (Holiday IS NULL AND Duty = 0 AND (Prev_Holiday = 'H' OR Next_Holiday = 'H'))
    	          THEN 2 ELSE 0 END AS New_Duty
    FROM CTE1
    )
    UPDATE CTE2
    SET Duty = New_Duty
    WHERE New_Duty = 2
    

    Best regards,

    Cosmog Hong


1 additional answer

Sort by: Most helpful
  1. LiHongMSFT-4306 30,671 Reputation points
    2023-07-06T02:15:53.63+00:00

    Hi @Analyst_SQL

    Try this:

    ;WITH CTE1 AS
    (
    SELECT R.*,M.Holiday
          ,LAG(Duty)OVER(PARTITION BY R.EnrollNumbeR ORDER BY R.Date) AS Prev_Duty
    	  ,LEAD(Duty)OVER(PARTITION BY R.EnrollNumbeR ORDER BY R.Date) AS Next_Duty
    FROM #EmpMaster E 
         JOIN #tbl_Emp_Register R ON E.EnrollNumber=R.EnrollNumbeR
         LEFT JOIN #MonthDate M ON R.Date = M.Date
    ),CTE2 AS
    (
    SELECT *,CASE WHEN Holiday='H' 
    	            OR (Day='Sat' AND Duty = 0 AND Next_Duty = 0)
    				OR (Day='Sun' AND Duty = 0 AND Next_Duty = 0 AND Prev_Duty = 0)
    				OR (Day='Mon' AND Duty = 0 AND Prev_Duty = 0)
    				OR (Holiday IS NULL AND Duty = 0 AND (Next_Duty = 0 OR Prev_Duty = 0))
    	          THEN 2 ELSE 0 END AS New_Duty
    FROM CTE1
    )
    UPDATE CTE2
    SET Duty = New_Duty
    WHERE New_Duty = 2
    

    Best regards,

    Cosmog Hong


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.