Sandwich is not applying,When Holiday is Marked 'H'

Analyst_SQL 3,551 Reputation points
2022-07-28T15:55:10.413+00:00

Below query working fine ,on define condition ,but not working on Condition 4 .

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 0 will be consider and Present will 1.
3) if any employee INTIME OR OUTIME exists in Saturday or Monday, and OUTTime Does not Exists, then that day and Sunday will be Absent, in Absent Column 1 will be consider and Present will 0.

4) if any employee came didn't come before Holiday(H) and After Holiday(H),then in Absent Column 1 will be consider and Present will 0. (as you can See Enroll Number 10002) ,Highlighted with red.

Create table #EmpMaster (EnrollNumber int, empName varchar(50))  
                 Create table #MonthDate (Day varchar(50),Date date,Holiday varchar(50))  
             Create table #AttendenceLOG (EnrollNumber int,A_Date datetime)  
                Insert into #EmpMaster values (10001,'ALi')  
         Insert into #EmpMaster values (10002,'Hussain')  
                   Insert into #MonthDate values ('Wednesday','2022-07-01',null)   
                   Insert into #MonthDate values ('Thursday','2022-07-02',null)   
                   Insert into #MonthDate values ('Friday','2022-07-03',null)   
                   Insert into #MonthDate values ('saturday','2022-07-04',null)   
                   Insert into #MonthDate values ('Sunday','2022-07-05',null)   
                   Insert into #MonthDate values ('Monday','2022-07-07',null)   
                   Insert into #MonthDate values ('Tuesday','2022-07-07',null)   
                   Insert into #MonthDate values ('Wednesday','2022-07-08',null)   
                   Insert into #MonthDate values ('Thursday','2022-07-09',null)   
                   Insert into #MonthDate values ('Friday','2022-07-10','H')   
                   Insert into #MonthDate values ('Saturday','2022-07-11','H')   
                   Insert into #MonthDate values ('Sunday','2022-07-12','H')   
                   Insert into #MonthDate values ('Monday','2022-07-13','H')   
                   Insert into #MonthDate values ('Tuesday','2022-07-14','H')   
                   Insert into #MonthDate values ('Wednesday','2022-07-15',null)  
                   Insert into #MonthDate values ('Thursday','2022-07-16',null)      
                   Insert into #MonthDate values ('Friday','2022-07-17',null)      
                   Insert into #MonthDate values ('Saturday','2022-07-18',null)      
                   Insert into #MonthDate values ('Sunday','2022-07-19',null)      
                   Insert into #MonthDate values ('Monday','2022-07-20',null)             
                   Insert into #MonthDate values ('Tuesday','2022-07-21',null)      
                    Insert into #MonthDate values ('Wednesday','2022-07-22',null)      
                         Insert into #MonthDate values ('Thursday','2022-07-23',null)  
                             Insert into #MonthDate values ('Friday','2022-07-24',null)  
                                 Insert into #MonthDate values ('Saturday','2022-07-25',null)  
                                    Insert into #MonthDate values ('Sunday','2022-07-26',null)  
                                        Insert into #MonthDate values ('Monday','2022-07-27',null)  
                                            Insert into #MonthDate values ('Tuesday','2022-07-28',null)  



              Insert into #AttendenceLOG values (10001,'2022-07-02 08:04:00')  
              Insert into #AttendenceLOG values (10001,'2022-07-02 17:45:00')  
              Insert into #AttendenceLOG values (10001,'2022-07-03 08:04:00')  
              Insert into #AttendenceLOG values (10001,'2022-07-03 17:45:00')  
                Insert into #AttendenceLOG values (10001,'2022-07-07 08:04:00')  
               Insert into #AttendenceLOG values (10001,'2022-07-09 07:45:00')  
              Insert into #AttendenceLOG values (10001,'2022-07-09 18:45:00')  

               Insert into #AttendenceLOG values (10001,'2022-07-15 08:35:00')  
               Insert into #AttendenceLOG values (10001,'2022-07-15 17:35:00')  
                Insert into #AttendenceLOG values (10001,'2022-07-16 08:35:00')  
               Insert into #AttendenceLOG values (10001,'2022-07-16 17:35:00')  
                Insert into #AttendenceLOG values (10001,'2022-07-17 08:35:00')  
                Insert into #AttendenceLOG values (10001,'2022-07-17 17:35:00')  
                Insert into #AttendenceLOG values (10001,'2022-07-25 08:35:00')  
                Insert into #AttendenceLOG values (10001,'2022-07-25 17:35:00')  

     Insert into #AttendenceLOG values (10002,'2022-07-02 08:04:00')  
              Insert into #AttendenceLOG values (10002,'2022-07-02 17:45:00')  
              Insert into #AttendenceLOG values (10002,'2022-07-03 08:04:00')  
              Insert into #AttendenceLOG values (10002,'2022-07-03 17:45:00')  
                Insert into #AttendenceLOG values (10002,'2022-07-07 08:04:00')  



                Insert into #AttendenceLOG values (10002,'2022-07-16 08:35:00')  
               Insert into #AttendenceLOG values (10002,'2022-07-16 17:35:00')  
                Insert into #AttendenceLOG values (10002,'2022-07-17 08:35:00')  
                Insert into #AttendenceLOG values (10002,'2022-07-17 17:35:00')  
                Insert into #AttendenceLOG values (10002,'2022-07-25 08:35:00')  
                Insert into #AttendenceLOG values (10002,'2022-07-25 17:35:00')  



Create table #EmpMaster (EnrollNumber int, empName varchar(50))  
                 Create table #MonthDate (Day varchar(50),Date date,Holiday varchar(50))  
             Create table #AttendenceLOG (EnrollNumber int,A_Date datetime)  
                Insert into #EmpMaster values (10001,'ALi')  
         Insert into #EmpMaster values (10002,'Hussain')  
                   Insert into #MonthDate values ('Wednesday','2022-07-01',null)   
                   Insert into #MonthDate values ('Thursday','2022-07-02',null)   
                   Insert into #MonthDate values ('Friday','2022-07-03',null)   
                   Insert into #MonthDate values ('saturday','2022-07-04',null)   
                   Insert into #MonthDate values ('Sunday','2022-07-05',null)   
                   Insert into #MonthDate values ('Monday','2022-07-07',null)   
                   Insert into #MonthDate values ('Tuesday','2022-07-07',null)   
                   Insert into #MonthDate values ('Wednesday','2022-07-08',null)   
                   Insert into #MonthDate values ('Thursday','2022-07-09',null)   
                   Insert into #MonthDate values ('Friday','2022-07-10','H')   
                   Insert into #MonthDate values ('Saturday','2022-07-11','H')   
                   Insert into #MonthDate values ('Sunday','2022-07-12','H')   
                   Insert into #MonthDate values ('Monday','2022-07-13','H')   
                   Insert into #MonthDate values ('Tuesday','2022-07-14','H')   
                   Insert into #MonthDate values ('Wednesday','2022-07-15',null)  
                   Insert into #MonthDate values ('Thursday','2022-07-16',null)      
                   Insert into #MonthDate values ('Friday','2022-07-17',null)      
                   Insert into #MonthDate values ('Saturday','2022-07-18',null)      
                   Insert into #MonthDate values ('Sunday','2022-07-19',null)      
                   Insert into #MonthDate values ('Monday','2022-07-20',null)             
                   Insert into #MonthDate values ('Tuesday','2022-07-21',null)      
                    Insert into #MonthDate values ('Wednesday','2022-07-22',null)      
                         Insert into #MonthDate values ('Thursday','2022-07-23',null)  
                             Insert into #MonthDate values ('Friday','2022-07-24',null)  
                                 Insert into #MonthDate values ('Saturday','2022-07-25',null)  
                                    Insert into #MonthDate values ('Sunday','2022-07-26',null)  
                                        Insert into #MonthDate values ('Monday','2022-07-27',null)  
                                            Insert into #MonthDate values ('Tuesday','2022-07-28',null)  



              Insert into #AttendenceLOG values (10001,'2022-07-02 08:04:00')  
              Insert into #AttendenceLOG values (10001,'2022-07-02 17:45:00')  
              Insert into #AttendenceLOG values (10001,'2022-07-03 08:04:00')  
              Insert into #AttendenceLOG values (10001,'2022-07-03 17:45:00')  
                Insert into #AttendenceLOG values (10001,'2022-07-07 08:04:00')  
               Insert into #AttendenceLOG values (10001,'2022-07-09 07:45:00')  
              Insert into #AttendenceLOG values (10001,'2022-07-09 18:45:00')  

               Insert into #AttendenceLOG values (10001,'2022-07-15 08:35:00')  
               Insert into #AttendenceLOG values (10001,'2022-07-15 17:35:00')  
                Insert into #AttendenceLOG values (10001,'2022-07-16 08:35:00')  
               Insert into #AttendenceLOG values (10001,'2022-07-16 17:35:00')  
                Insert into #AttendenceLOG values (10001,'2022-07-17 08:35:00')  
                Insert into #AttendenceLOG values (10001,'2022-07-17 17:35:00')  
                Insert into #AttendenceLOG values (10001,'2022-07-25 08:35:00')  
                Insert into #AttendenceLOG values (10001,'2022-07-25 17:35:00')  

     Insert into #AttendenceLOG values (10002,'2022-07-02 08:04:00')  
              Insert into #AttendenceLOG values (10002,'2022-07-02 17:45:00')  
              Insert into #AttendenceLOG values (10002,'2022-07-03 08:04:00')  
              Insert into #AttendenceLOG values (10002,'2022-07-03 17:45:00')  
                Insert into #AttendenceLOG values (10002,'2022-07-07 08:04:00')  



                Insert into #AttendenceLOG values (10002,'2022-07-16 08:35:00')  
               Insert into #AttendenceLOG values (10002,'2022-07-16 17:35:00')  
                Insert into #AttendenceLOG values (10002,'2022-07-17 08:35:00')  
                Insert into #AttendenceLOG values (10002,'2022-07-17 17:35:00')  
                Insert into #AttendenceLOG values (10002,'2022-07-25 08:35:00')  
                Insert into #AttendenceLOG values (10002,'2022-07-25 17:35:00')  


 ;WITH CTE AS   
 (  
  SELECT a.EnrollNumber,b.Date,b.Day,c.A_Date ,a.EmpName,b.Holiday  
  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,Holiday,MIN(A_Date) [IN(A_Date)],MAX(A_Date) [OUT(A_Date)]  
  FROM CTE   
  GROUP BY EnrollNumbeR,Date,Day,EmpName,Holiday  
 ),CTE2 AS   
 (  
  SELECT EnrollNumbeR,Date,LEFT(Day,3)Day,EmpName,Holiday  
        ,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,Holiday,[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)])>=5  
              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)])>=5 THEN 'P'  
              ELSE 'A' END [STATUS]  
  FROM CTE2  
 ),CTE4 AS (  
  SELECT EnrollNumbeR,EmpName,Date,Day,[HOUR],Holiday,[IN(A_Date)],[OUT(A_Date)],A_Date  
        ,CASE WHEN [DAY] ='Sun' AND (LAG([STATUS])OVER(ORDER BY EnrollNumbeR,Date)='P' OR LEAD([STATUS])OVER(ORDER BY EnrollNumbeR,Date)='P') THEN 'S'   
   When Holiday='H' THEN 'H'  
              ELSE [STATUS] END [STATUS]  
  FROM CTE3   
 )  
 SELECT EnrollNumbeR,EmpName,Date,Day,[HOUR]  
       ,CASE WHEN STATUS='A' THEN 1   
             WHEN STATUS='S' THEN 0   
  WHEN STATUS='H' 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 STATUS='H' 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 '2022-07-07' and '2022-07-25'  
 ORDER BY EnrollNumbeR,Date

225830-sendwich.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.
12,823 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
Accepted answer
  1. LiHong-MSFT 10,046 Reputation points
    2022-08-08T07:29:23.28+00:00

    Hi @Analyst_SQL
    Try this query: 

    ;WITH CTE AS   
(  
 SELECT a.EnrollNumber,b.Date,b.Day,c.A_Date ,a.EmpName,b.Holiday  
 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,Holiday,MIN(A_Date) [IN(A_Date)],MAX(A_Date) [OUT(A_Date)]  
 FROM CTE   
 GROUP BY EnrollNumbeR,Date,Day,EmpName,Holiday  
),CTE2 AS   
(  
 SELECT EnrollNumbeR,Date,LEFT(Day,3)Day,EmpName,Holiday  
       ,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,Holiday,[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)])>=5  
             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)])>=5 THEN 'P'  
             ELSE 'A' END [STATUS]  
 FROM CTE2  
),CTE4 AS   
(  
 SELECT EnrollNumbeR,EmpName,Date,Day,[HOUR],Holiday,[IN(A_Date)],[OUT(A_Date)],A_Date  
       ,CASE WHEN [DAY] ='Sun' AND (LAG([STATUS])OVER(ORDER BY EnrollNumbeR,Date)='P' OR LEAD([STATUS])OVER(ORDER BY EnrollNumbeR,Date)='P') THEN 'S'   
             When Holiday='H' THEN 'H' ELSE [STATUS] END [STATUS]  
 FROM CTE3   
),CTE5 AS  
(  
 SELECT EnrollNumbeR,EmpName,Date,Day,[HOUR]  
       ,CASE WHEN STATUS='A' THEN 1   
             WHEN STATUS='S' THEN 0   
             WHEN STATUS='H' 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 STATUS='H' 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  
),CTE6 AS(  
 SELECT *,CASE WHEN STATUS='H' AND LAG(STATUS)OVER(ORDER BY EnrollNumbeR,Date)='A' THEN 1   
               WHEN STATUS='A' AND LAG(STATUS)OVER(ORDER BY EnrollNumbeR,Date)='H' THEN 1  
			   ELSE 0 END AS TO_SUM  
 FROM CTE5  
),CTE7 AS  
(  
 SELECT *,SUM(TO_SUM)OVER(ORDER BY EnrollNumbeR ,Date ROWS UNBOUNDED PRECEDING)AS PART_VALUE  
 FROM CTE6  
)  
SELECT EnrollNumber,EmpName,Date,Day,HOUR  
      ,CASE WHEN PART_VALUE IN (SELECT PART_VALUE FROM CTE7 WHERE STATUS='H' AND TO_SUM=1) THEN 1 ELSE Absent END AS Absent  
	  ,CASE WHEN PART_VALUE IN (SELECT PART_VALUE FROM CTE7 WHERE STATUS='H' AND TO_SUM=1) THEN 0 ELSE Present END AS Present  
	  ,CASE WHEN PART_VALUE IN (SELECT PART_VALUE FROM CTE7 WHERE STATUS='H' AND TO_SUM=1) THEN 'A' ELSE STATUS END AS STATUS  
	  ,CASE WHEN PART_VALUE IN (SELECT PART_VALUE FROM CTE7 WHERE STATUS='H' AND TO_SUM=1) THEN 'A' ELSE [IN(A_Date)] END AS [IN(A_Date)]  
FROM CTE7  
WHERE Date BETWEEN '2022-07-07' and '2022-07-25'  
ORDER BY EnrollNumbeR,Date

    Best regards,
    LiHong

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest