sandwich holiday is not working

asked 2022-06-22T14:19:14.26+00:00
akhter hussain 2,491 Reputation points

I want sandwich of holidays Means,

Saturday ,Sunday ,Monday,

1) if any employee did not come on above mentioned days, Saturday and Monday, then system mark Sunday as Absent(A),in Absent Column 1 will be consider and Present will 0.
2) if any employee came on Saturday or Monday then Sunday will be marked as (S),in Absent Column 1 will be consider and Present will 0.
3) if any employee came on INTIME exist and OUT Time Does not Exist then that day and Sunday will be Absent, in Absent Column 1 will be consider and Present will 0.

I attached image, in which i highlighted with Red rectangle

Create table #EmpMaster (EnrollNumber int, empName varchar(50))  
               Create table #MonthDate (Day varchar(50),Date date)  
           Create table #AttendenceLOG (EnrollNumber int,A_Date datetime)  
              Insert into #EmpMaster values (10001,'ALi')  

     Insert into #MonthDate values ('Wednesday','2022-06-01')   
                 Insert into #MonthDate values ('Thursday','2022-06-02')   
                 Insert into #MonthDate values ('Friday','2022-06-03')   
                 Insert into #MonthDate values ('saturday','2022-06-04')   
                 Insert into #MonthDate values ('Sunday','2022-06-05')   
                 Insert into #MonthDate values ('Monday','2022-06-06')   
                 Insert into #MonthDate values ('Tuesday','2022-06-07')   
                 Insert into #MonthDate values ('Wednesday','2022-06-08')   
                 Insert into #MonthDate values ('Thursday','2022-06-09')   
                 Insert into #MonthDate values ('Friday','2022-06-10')   
                 Insert into #MonthDate values ('Saturday','2022-06-11')   
                 Insert into #MonthDate values ('Sunday','2022-06-12')   
                 Insert into #MonthDate values ('Monday','2022-06-13')   
                 Insert into #MonthDate values ('Tuesday','2022-06-14')   
                 Insert into #MonthDate values ('Wednesday','2022-06-15')  
                 Insert into #MonthDate values ('Thursday','2022-06-16')      
                 Insert into #MonthDate values ('Friday','2022-06-17')    
                 Insert into #MonthDate values ('Saturday','2022-06-18')      
                 Insert into #MonthDate values ('Sunday','2022-06-19')    
                 Insert into #MonthDate values ('Monday','2022-06-20')           
             Insert into #MonthDate values ('Tuesday','2022-06-21')   
                 Insert into #MonthDate values ('Wednesday','2022-06-22')     
                     Insert into #MonthDate values ('Thursday','2022-06-23')  
                         Insert into #MonthDate values ('Friday','2022-06-24')  
                             Insert into #MonthDate values ('Saturday','2022-06-25')  
                                 Insert into #MonthDate values ('Sunday','2022-06-26')  
                                     Insert into #MonthDate values ('Monday','2022-06-27')  
                                         Insert into #MonthDate values ('Tuesday','2022-06-28')  



            Insert into #AttendenceLOG values (10001,'2022-06-02 08:04:00')  
            Insert into #AttendenceLOG values (10001,'2022-06-02 17:45:00')  
            Insert into #AttendenceLOG values (10001,'2022-06-03 08:04:00')  
            Insert into #AttendenceLOG values (10001,'2022-06-03 17:45:00')  
              Insert into #AttendenceLOG values (10001,'2022-06-06 08:04:00')  
             Insert into #AttendenceLOG values (10001,'2022-06-09 07:45:00')  
            Insert into #AttendenceLOG values (10001,'2022-06-09 18:45:00')  
            Insert into #AttendenceLOG values (10001,'2022-06-10 08:35:00')  
             Insert into #AttendenceLOG values (10001,'2022-06-10 17:35:00')  
              Insert into #AttendenceLOG values (10001,'2022-06-11 08:35:00')  
             Insert into #AttendenceLOG values (10001,'2022-06-11 17:35:00')  
              Insert into #AttendenceLOG values (10001,'2022-06-14 08:35:00')  
             Insert into #AttendenceLOG values (10001,'2022-06-14 17:35:00')  
             Insert into #AttendenceLOG values (10001,'2022-06-15 08:35:00')  
             Insert into #AttendenceLOG values (10001,'2022-06-15 17:35:00')  
              Insert into #AttendenceLOG values (10001,'2022-06-16 08:35:00')  
             Insert into #AttendenceLOG values (10001,'2022-06-16 17:35:00')  
              Insert into #AttendenceLOG values (10001,'2022-06-17 08:35:00')  
              Insert into #AttendenceLOG values (10001,'2022-06-17 17:35:00')  
              Insert into #AttendenceLOG values (10001,'2022-06-25 08:35:00')  
              Insert into #AttendenceLOG values (10001,'2022-06-25 17:35:00')  

I attached query 213908-query.txt

213924-ragister.jpg

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,473 questions
No comments
1 vote

Accepted answer
  1. answered 2022-06-23T02:35:24.343+00:00
    LiHong-MSFT 9,986 Reputation points

    Hi @akhter hussain
    Check this:

    Declare @DateFrom  date='2022-06-01'   
    Declare @Dateto  date='2022-06-28'   
      
    ;WITH CTE AS   
    (  
     SELECT a.EnrollNumber,b.Date,b.Day,c.A_Date ,a.EmpName  
     FROM #EmpMaster a CROSS JOIN #MonthDate b  
                       LEFT JOIN #AttendenceLOG c ON a.EnrollNumber=c.EnrollNumber AND cast(c.A_Date as date)=b.Date  
    ),CTE1 AS   
    (  
     SELECT  EnrollNumbeR,Date,Day,EmpName,MIN(A_Date) [IN(A_Date)],MAX(A_Date) [OUT(A_Date)]  
     FROM CTE   
     GROUP BY EnrollNumbeR,Date,Day,EmpName  
    ),CTE2 AS   
    (  
     SELECT EnrollNumbeR,Date,LEFT(Day,3)Day,EmpName  
           ,CASE WHEN [IN(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([IN(A_Date)] AS DATE) AS DATETIME))   
    	         THEN NULL ELSE [IN(A_Date)] END [IN(A_Date)]  
           ,CASE WHEN [OUT(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([OUT(A_Date)] AS DATE) AS DATETIME))   
    	         THEN [OUT(A_Date)] ELSE NULL END [OUT(A_Date)]  
     FROM CTE1  
    ),CTE3 AS (  
     SELECT EnrollNumbeR,Date,Day,EmpName,[IN(A_Date)],[OUT(A_Date)]  
           ,CASE WHEN [IN(A_Date)] IS NOT NULL AND [OUT(A_Date)] IS NOT NULL AND DATEDIFF(HOUR,[IN(A_Date)],[OUT(A_Date)])>=8   
                 THEN CAST(CAST(DATEDIFF(MINUTE,[IN(A_Date)],[OUT(A_Date)]) AS FLOAT)/60 AS decimal(10,2))   
    			 ELSE 0 END [HOUR]  
           ,CASE WHEN  [IN(A_Date)] IS NOT NULL OR [OUT(A_Date)] IS NOT NULL   
                 THEN CONCAT(FORMAT([IN(A_Date)],'yyyy-MM-dd hh:mm'),CHAR(10),FORMAT([OUT(A_Date)],'yyyy-MM-dd hh:mm'))  
                 ELSE NULL END [A_Date]  
    	   ,CASE WHEN [IN(A_Date)] IS NULL AND [OUT(A_Date)] IS NULL THEN 'A'  
                 WHEN DATEDIFF(HOUR,[IN(A_Date)],[OUT(A_Date)])>=8 THEN 'P'  
                 ELSE 'A' END [STATUS]  
     FROM CTE2  
    ),CTE4 AS (  
     SELECT EnrollNumbeR,EmpName,Date,Day,[HOUR],[IN(A_Date)],[OUT(A_Date)],A_Date  
           ,CASE WHEN [DAY] ='Sun' AND (LAG([STATUS])OVER(ORDER BY Date)='P' OR LEAD([STATUS])OVER(ORDER BY Date)='P') THEN 'S'   
    	         ELSE [STATUS] END [STATUS]  
     FROM CTE3   
    )  
    SELECT EnrollNumbeR,EmpName,Date,Day,[HOUR]  
          ,CASE WHEN STATUS='A' THEN 1   
    	        WHEN STATUS='S' THEN 0   
    			WHEN [IN(A_Date)] IS NULL OR [OUT(A_Date)] IS  NULL THEN 1   
    			ELSE 0 END [Absent]  
    	  ,CASE WHEN STATUS='A' THEN 0   
    	        WHEN STATUS='S' THEN 1   
    			WHEN [IN(A_Date)] IS NULL OR [OUT(A_Date)] IS NULL THEN 0     
    			ELSE 1 END [Present]  
    	  ,STATUS,ISNULL([A_Date],STATUS) AS [IN(A_Date)]  
    FROM CTE4  
    WHERE Date BETWEEN @DateFrom and @DateTo --and EnrollNumber=@EmpID  
    ORDER BY EnrollNumbeR,Date    
    

    Best regards,
    LiHong


1 additional answer

Sort by: Most helpful
  1. answered 2022-06-24T14:08:10.483+00:00
    akhter hussain 2,491 Reputation points

    @LiHong-MSFT

    Your Query is working fine on dummy data, but when i execute query on my actual data, then Sunday is marking absent ,while IN/OUT time exists

    I have A_Date value in this form 2022-05-18 17:19:00.000

    214807-image.png