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