Update Column base on date between.

Analyst_SQL 3,531 Reputation points
2023-07-17T14:00:27.8666667+00:00

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)   




 


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.
12,313 questions
0 comments No comments
{count} votes

Accepted answer
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-07-19T01:34:37.2666667+00:00

    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;
    

1 additional answer

Sort by: Most helpful
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-07-18T01:57:57.37+00:00

    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:

    User's image

    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.