Update Column base on date between.

Question

I have already asked this question, but i changes in my table of #MonthDate,please go through

I want ,when day(15-06-2022) before From_Date, Duty is zero and after day(20-06-2022) from To_Date Duty is Zero ,then From_Date and To_Date ,Duty Column will be marked with 2 else if 15-06-2022 or 20-06-2022 is marked with 1 then From_Date and To_Date ,Duty Column will be marked with 1.

below is data

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

 Create table #tbl_Emp_Register (EnrollNumbeR int,Date date,Day varchar(5),Duty int)    

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




 

Answer 1

Hi @Analyst_SQL

Try this.

;with T1 as(
  select From_Date,To_Date from #MonthDate where Holiday = 'H' group by From_Date,To_Date
),T2 as(
  select * from #tbl_Emp_Register as A cross join T1 as B
  where A.Date = dateadd(d,-1,B.From_Date) or A.Date = dateadd(d,1,B.To_Date)
),T3 as(
  select EnrollNumbeR,sum(Duty) as su from T2 group by EnrollNumbeR
),T4 as(
  select r.EnrollNumbeR,r.Date,r.Duty,s.From_Date,s.To_Date,
         case when r.Date between s.From_Date and s.To_Date and t.su = 0 then 2 
              when r.Date between s.From_Date and s.To_Date and t.su > 0 then 1 
         else r.Duty end as D
  from #tbl_Emp_Register as r cross join T1 as s 
  left join (select EnrollNumbeR,su from T3) as t on r.EnrollNumbeR = t.EnrollNumbeR)
update T4 set Duty = D;

Answer 2

Hi @Analyst_SQL

The desired output you provided and your description seem to conflict.

else if 15-06-2022 or 20-06-2022 is marked with 1 then From_Date and To_Date ,Duty Column will be marked with 1.

In the original table, 2022-11-15 is marked as 1, so From_Date and To_Date ,Duty Column will be marked with 1.

If what you need is the desired output you show, you can try this.

;with T1 as(
  select From_Date,To_Date from #MonthDate where Holiday = 'H' group by From_Date,To_Date
),T2 as(
  select *,case when A.Date between B.From_Date and B.To_Date then 2 else A.Duty end as D
  from #tbl_Emp_Register as A cross join T1 as B)
update T2 set Duty = D;

select * from #tbl_Emp_Register;

Output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.