Create table #EmpMaster (EnrollNumber int, empName varchar(50))
               Create table #MonthDate (Day varchar(50),D_Date date)
           Create table #AttendenceLOG (EnrollNumber int,A_Date datetime)
              
			  Insert into #EmpMaster values (10001,'ALi')
        Insert into #EmpMaster values (10002,'ALi')
	      Insert into #MonthDate values ('Tuesday','2021-06-01')             
                Insert into #MonthDate values ('Wednesday','2021-06-02') 
                    Insert into #MonthDate values ('Thursday','2021-06-03') 
                       Insert into #MonthDate values ('Friday','2021-06-04') 
                                   Insert into #MonthDate values ('Saturday','2021-06-05')             
                Insert into #MonthDate values ('Sunday','2021-06-06') 
                    Insert into #MonthDate values ('Monday','2021-06-07') 
                       Insert into #MonthDate values ('Tuesday','2021-06-08') 
                          Insert into #MonthDate values ('Wednesday','2021-06-09') 
						          Insert into #MonthDate values ('Thursday','2021-06-10') 
                       Insert into #MonthDate values ('Friday','2021-06-11') 
                                   Insert into #MonthDate values ('Saturday','2021-06-12')             
                Insert into #MonthDate values ('Sunday','2021-06-13') 
				  Insert into #MonthDate values ('Monday','2021-06-14') 
                       Insert into #MonthDate values ('Tuesday','2021-06-15') 
                          Insert into #MonthDate values ('Wednesday','2021-06-16') 
						          Insert into #MonthDate values ('Thursday','2021-06-17') 
                       Insert into #MonthDate values ('Friday','2021-06-18') 
                                   Insert into #MonthDate values ('Saturday','2021-06-19') 
								       Insert into #MonthDate values ('Sunday','2021-06-20') 
								    Insert into #MonthDate values ('Monday','2021-06-21') 
                       Insert into #MonthDate values ('Tuesday','2021-06-22') 
                          Insert into #MonthDate values ('Wednesday','2021-06-23') 
						          Insert into #MonthDate values ('Thursday','2021-06-24') 
                       Insert into #MonthDate values ('Friday','2021-06-25') 
					          Insert into #MonthDate values ('Saturday','2021-06-26')  
						   Insert into #MonthDate values ('Sunday','2021-06-27') 
                               
								    Insert into #MonthDate values ('Monday','2021-06-28') 
                       Insert into #MonthDate values ('Tuesday','2021-06-29') 
                          Insert into #MonthDate values ('Wednesday','2021-06-30') 
                    
                       
            Insert into #AttendenceLOG values (10001,'2021-06-01 08:00:00.000')
             Insert into #AttendenceLOG values (10001,'2021-06-01 18:00:00.000')
                 Insert into #AttendenceLOG values (10001,'2021-06-09 10:04:00')
                Insert into #AttendenceLOG values (10001,'2021-06-09 20:04:00')
          
           
				     Insert into #AttendenceLOG values (10001,'2021-06-07 20:35:00')
					   Insert into #AttendenceLOG values (10001,'2021-06-10 08:00:00.000')
             Insert into #AttendenceLOG values (10001,'2021-06-10 18:00:00.000')
                 Insert into #AttendenceLOG values (10001,'2021-06-11 10:04:00')
                Insert into #AttendenceLOG values (10001,'2021-06-11 20:04:00')
             Insert into #AttendenceLOG values (10001,'2021-06-15 07:45:00')
               Insert into #AttendenceLOG values (10001,'2021-06-15 21:45:00')
                  Insert into #AttendenceLOG values (10001,'2021-06-16 08:35:00')
				     Insert into #AttendenceLOG values (10001,'2021-06-16 20:35:00')
 
 ;with cte as (
 select a.EnrollNumber,b.D_Date,b.Day,c.A_Date 
 from #EmpMaster a 
 cross join #MonthDate b
 left join #AttendenceLOG c 
 on a.EnrollNumber=c.EnrollNumber 
 and cast(c.A_Date as date)=b.D_Date)
 ,cte1 AS (
 SELECT  EnrollNumbeR,D_Date,Day
 ,MIN(A_Date) [IN(A_Date)],MAX(A_Date) [OUT(A_Date)]
 FROM CTE 
 GROUP BY EnrollNumbeR,D_Date,Day)
 ,CTE2 AS (
 SELECT EnrollNumbeR,D_Date,Day
 ,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 FROM CTE2
 WHERE ([IN(A_Date)] IS NOT NULL OR [OUT(A_Date)] IS NOT NULL)
 AND [DAY] IN ('Saturday','Monday'))
 ,CTE3 AS (
 SELECT *
 ,CASE WHEN [DAY] ='Sunday' AND EnrollNumbeR IN (SELECT EnrollNumbeR FROM SUNDAY)   THEN 'S'
     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,
 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]
 FROM CTE2)
 SELECT EnrollNumber,D_Date,Day,
 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:ss tt'),CHAR(10),FORMAT([OUT(A_Date)],'yyyy-MM-dd hh:mm:ss tt'))
 ELSE [STATUS] END [IN(A_Date)]
 ,HOUR
 FROM CTE3
 ORDER BY EnrollNumbeR,D_Date