Apply Date filter on below query?

Question

as per below link https://learn.microsoft.com/en-us/answers/questions/1331766/update-column-base-on-date-between,

i am applying date filter in query ,but output is not displaying correctly

Condition

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.

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)   


 Insert into #tbl_Emp_Register values (10002,'2022-11-12','Sun',1)    				
Insert into #tbl_Emp_Register values (10002,'2022-11-13','Mon',0)    			    
Insert into #tbl_Emp_Register values (10002,'2022-11-14','Tue' ,1)   				 				
Insert into #tbl_Emp_Register values (10002,'2022-11-15','Wed' ,0)                                  
Insert into #tbl_Emp_Register values (10002,'2022-11-16' ,'Thu',0)                        
 Insert into #tbl_Emp_Register values (10002,'2022-11-17','Fri',0)                   				
 Insert into #tbl_Emp_Register values (10002,'2022-11-18','Sat',0)    				
Insert into #tbl_Emp_Register values (10002,'2022-11-19','Sun',0)    				
Insert into #tbl_Emp_Register values (10002,'2022-11-20','Mon',0)    				
 Insert into #tbl_Emp_Register values (10002,'2022-11-21','Tue' ,1)  



 ;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
  where r.date between '2023-11-12' and '2023-11-21')
update T4 set Duty = D;

select * from #tbl_Emp_Register;

Answer 1

Hi @Analyst_SQL

I still can not figure out why this script does not meet the requirements.

;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
  where r.date between '2022-11-12' and '2023-11-21')
update T4 set Duty = D;

Answer 2

You seem to want to update the "Duty" column in the `#tbl_Emp_Register` table based on specific conditions related to dates from the `#MonthDate` table. Specifically, if a date is before the "From_Date" or after the "To_Date" and the "Duty" is 0, then set the "Duty" to 2. Otherwise, if the "Duty" is 1, set the "Duty" to 1.

Upon examining the SQL code, it appears to be implementing this logic. However, a small issue in the code with the `T2` common table expression (CTE) affects the final result. The join condition in `T2` needs to be modified to filter the relevant records correctly. 


;WITH T1 AS (
    SELECT From_Date, To_Date
    FROM #MonthDate
    WHERE Holiday = 'H'
), 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 < s.From_Date OR r.Date > s.To_Date THEN
                   CASE
                       WHEN r.Duty = 0 THEN 2
                       ELSE r.Duty
                   END
               ELSE
                   CASE
                       WHEN r.Duty = 1 THEN 1
                       ELSE r.Duty
                   END
           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
    WHERE r.date BETWEEN '2022-11-12' AND '2022-11-21'
)
UPDATE T4 SET Duty = D;

SELECT * FROM #tbl_Emp_Register;