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)
 ,SUNDAY AS (
 SELECT EnrollNumbeR,EmpName,datepart(week, Date) weekno FROM CTE2
 WHERE ([IN(A_Date)] IS NOT NULL OR [OUT(A_Date)] IS NOT NULL)
 AND [DAY] IN ('Sat','Mon')
 )
 ,CTE3 AS (
 SELECT  distinct a.*
 ,CASE WHEN a.[DAY] ='Sun' AND b.EnrollNumbeR is not null   THEN 'S'
     WHEN a.[IN(A_Date)] IS NULL AND a.[OUT(A_Date)] IS NULL THEN 'A'
 WHEN DATEDIFF(HOUR,a.[IN(A_Date)],a.[OUT(A_Date)])>=8 THEN 'P'
 ELSE 'A' END STATUS,
 CASE WHEN a.[IN(A_Date)] IS NOT NULL AND a.[OUT(A_Date)] IS NOT NULL AND DATEDIFF(HOUR,a.[IN(A_Date)],a.[OUT(A_Date)])>=8 
 THEN CAST(CAST(DATEDIFF(MINUTE,a.[IN(A_Date)],a.[OUT(A_Date)]) AS FLOAT)/60 AS decimal(10,2)) ELSE 0 END [HOUR]
 FROM CTE2 a left join SUNDAY b on a.EnrollNumbeR=b.EnrollNumbeR and datepart(week, a.date)=b.weekno 
 )
 SELECT EnrollNumber,EmpName,Date,Day,
 --CASE WHEN  [IN(A_Date)] IS NOT NULL OR [OUT(A_Date)] IS NOT NULL  THEN CONCAT(FORMAT([IN(A_Date)],'hh:mm'),CHAR(10),FORMAT([OUT(A_Date)],' hh:mm'))ELSE [STATUS] END [IN(A_Date)],
 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 [STATUS],
   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 [STATUS] END [IN(A_Date)]
 FROM CTE3
 where date between @DateFrom and @DateTo --and EnrollNumber=@EmpID
 ORDER BY EnrollNumbeR,Date