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,'Mirab')
		     Insert into #EmpMaster values (10003,'Khan')
			 			  Insert into #EmpMaster values (10004,'Kami')
        Insert into #EmpMaster values (10005,'Misam')
		     Insert into #EmpMaster values (10006,'Kosar')
			         Insert into #EmpMaster values (10007,'Shani')
		     Insert into #EmpMaster values (10008,'Irfan')

	      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')

					   Insert into #AttendenceLOG values (10002,'2021-06-01 08:00:00.000')
             Insert into #AttendenceLOG values (10002,'2021-06-01 18:00:00.000')
                 Insert into #AttendenceLOG values (10002,'2021-06-09 10:04:00')
                Insert into #AttendenceLOG values (10002,'2021-06-09 20:04:00')
          
           
				     Insert into #AttendenceLOG values (10002,'2021-06-07 20:35:00')
					   Insert into #AttendenceLOG values (10002,'2021-06-10 08:00:00.000')
             Insert into #AttendenceLOG values (10002,'2021-06-10 18:00:00.000')
                 Insert into #AttendenceLOG values (10002,'2021-06-11 10:04:00')
                Insert into #AttendenceLOG values (10002,'2021-06-11 20:04:00')


					 
				     Insert into #AttendenceLOG values (10008,'2021-06-07 20:35:00')
					   Insert into #AttendenceLOG values (10008,'2021-06-10 08:00:00.000')
             Insert into #AttendenceLOG values (10008,'2021-06-10 18:00:00.000')
                 Insert into #AttendenceLOG values (10008,'2021-06-11 10:04:00')
                Insert into #AttendenceLOG values (10008,'2021-06-11 20:04:00')
             Insert into #AttendenceLOG values (10008,'2021-06-15 07:45:00')
               Insert into #AttendenceLOG values (10008,'2021-06-15 21:45:00')
                  Insert into #AttendenceLOG values (10008,'2021-06-16 08:35:00')
				     Insert into #AttendenceLOG values (10008,'2021-06-16 20:35:00')


					   Insert into #AttendenceLOG values (10003,'2021-06-01 08:00:00.000')
             Insert into #AttendenceLOG values (10003,'2021-06-01 18:00:00.000')
                 Insert into #AttendenceLOG values (10003,'2021-06-09 10:04:00')
                Insert into #AttendenceLOG values (10003,'2021-06-09 20:04:00')
          
           
				     Insert into #AttendenceLOG values (10003,'2021-06-07 20:35:00')
					   Insert into #AttendenceLOG values (10003,'2021-06-10 08:00:00.000')
             Insert into #AttendenceLOG values (10003,'2021-06-10 18:00:00.000')
                 Insert into #AttendenceLOG values (10003,'2021-06-11 10:04:00')
                Insert into #AttendenceLOG values (10003,'2021-06-11 20:04:00')
             Insert into #AttendenceLOG values (10003,'2021-06-15 07:45:00')
               Insert into #AttendenceLOG values (10003,'2021-06-15 21:45:00')
                  Insert into #AttendenceLOG values (10003,'2021-06-16 08:35:00')
				     Insert into #AttendenceLOG values (10003,'2021-06-16 20:35:00')

					 			   Insert into #AttendenceLOG values (10007,'2021-06-01 08:00:00.000')
             Insert into #AttendenceLOG values (10007,'2021-06-01 18:00:00.000')
          
		         Insert into #AttendenceLOG values (10007,'2021-06-02 10:04:00')
                Insert into #AttendenceLOG values (10007,'2021-06-02 20:04:00')

				       Insert into #AttendenceLOG values (10007,'2021-06-03 10:04:00')
                Insert into #AttendenceLOG values (10007,'2021-06-03 20:04:00')
				       Insert into #AttendenceLOG values (10007,'2021-06-04 10:04:00')
                Insert into #AttendenceLOG values (10007,'2021-06-04 20:04:00')

				       Insert into #AttendenceLOG values (10007,'2021-06-05 10:04:00')
                Insert into #AttendenceLOG values (10007,'2021-06-05 20:04:00')


				       Insert into #AttendenceLOG values (10007,'2021-06-28 10:04:00')
                Insert into #AttendenceLOG values (10007,'2021-06-28 20:04:00')

				       Insert into #AttendenceLOG values (10007,'2021-06-29 10:04:00')
                Insert into #AttendenceLOG values (10007,'2021-06-29 20:04:00')

					       Insert into #AttendenceLOG values (10007,'2021-06-30 10:04:00')
                Insert into #AttendenceLOG values (10007,'2021-06-30 20:04:00')


		         Insert into #AttendenceLOG values (10007,'2021-06-09 10:04:00')
                Insert into #AttendenceLOG values (10007,'2021-06-09 20:04:00')
          
           
				     Insert into #AttendenceLOG values (10007,'2021-06-07 20:35:00')
					   Insert into #AttendenceLOG values (10007,'2021-06-10 08:00:00.000')
             Insert into #AttendenceLOG values (10007,'2021-06-10 18:00:00.000')
                 Insert into #AttendenceLOG values (10007,'2021-06-11 10:04:00')
                Insert into #AttendenceLOG values (10007,'2021-06-11 20:04:00')
             Insert into #AttendenceLOG values (10007,'2021-06-15 07:45:00')
               Insert into #AttendenceLOG values (10007,'2021-06-15 21:45:00')
                  Insert into #AttendenceLOG values (10007,'2021-06-16 08:35:00')
				     Insert into #AttendenceLOG values (10007,'2021-06-16 20:35:00')


					 			   
					   Insert into #AttendenceLOG values (10001,'2021-06-21 08:00:00.000')
             Insert into #AttendenceLOG values (10001,'2021-06-21 18:00:00.000')
                 Insert into #AttendenceLOG values (10001,'2021-06-22 10:04:00')
                Insert into #AttendenceLOG values (10001,'2021-06-22 20:04:00')
             Insert into #AttendenceLOG values (10001,'2021-06-23 07:45:00')
               Insert into #AttendenceLOG values (10001,'2021-06-23 21:45:00')
                  Insert into #AttendenceLOG values (10001,'2021-06-24 08:35:00')
				     Insert into #AttendenceLOG values (10001,'2021-06-24 20:35:00')
					          Insert into #AttendenceLOG values (10001,'2021-06-25 07:45:00')
               Insert into #AttendenceLOG values (10001,'2021-06-25 21:45:00')
                  Insert into #AttendenceLOG values (10001,'2021-06-26 08:35:00')
				     Insert into #AttendenceLOG values (10001,'2021-06-26 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 *,datepart(week, d_date) weekno FROM CTE2
   WHERE ([IN(A_Date)] IS NOT NULL OR [OUT(A_Date)] IS NOT NULL)
   AND [DAY] IN ('Saturday','Monday')
   )
   ,CTE3 AS (
   SELECT a.*
   ,CASE WHEN a.[DAY] ='Sunday' 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.d_date)=b.weekno
   )
   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