IN(A_Date) Date time is not showing.

Question

@LiHong-MSFT

as per you giving solution below
sandwich-is-not-applyingwhen-holiday-is-marked-39h.html

it is not working properly as you can in image,EnrollNumber 10001 Hours is being calculated (Highlighted Red),but IN(A_Date) values is not displaying,as you can
see enrollnumber 10004 IN(A_Date) is displaying ,10001 IN(A_Date ) values is existing in table ,then its hours is being calculating.

Answer 1

Hi @akhter hussain
Please check this modification:
Modify Line 55 from this: WHEN STATUS='A' AND LAG(STATUS)OVER(ORDER BY EnrollNumbeR,Date)='H' THEN 1 to this: WHEN LAG(STATUS,1,'H')OVER(ORDER BY EnrollNumbeR,Date)='H' THEN 1

Answer 2

@CosmogHong-MSFT

I am attaching data with table and 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-09-07' and '2022-09-25' and EnrollNumber=10001  
 ORDER BY EnrollNumbeR,Date    

view