Share via

sandwich holiday is not working

Analyst_SQL 3,551 Reputation points
2022-06-22T14:19:14.26+00:00

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.
13,364 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
0 comments
Accepted answer
  1. LiHong-MSFT 10,046 Reputation points
    2022-06-23T02:35:24.343+00:00

    Hi @Analyst_SQL
    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 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Analyst_SQL 3,551 Reputation points
    2022-06-24T14:08:10.483+00:00

    @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