I want to display TimeIN ,Timeout and Absent in one column,
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,'Kami')
Insert into #EmpMaster values (10003,'Jhon')
Insert into #EmpMaster values (10004,'Muli')
Insert into #EmpMaster values (10005,'Rose')
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 #AttendenceLOG values (10001,'2021-06-09 08:04:00')
Insert into #AttendenceLOG values (10001,'2021-06-09 17:45:00')
Insert into #AttendenceLOG values (10002,'2021-06-09 10:04:00')
Insert into #AttendenceLOG values (10003,'2021-06-09 20:04:00')
Insert into #AttendenceLOG values (10004,'2021-06-09 07:45:00')
Insert into #AttendenceLOG values (10004,'2021-06-09 09:45:00')
Insert into #AttendenceLOG values (10004,'2021-06-09 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)
,CET3 as (
SELECT *
,CASE WHEN [DAY] ='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 NULL AND [OUT(A_Date)] IS NULL AND [STATUS]='A'
THEN [STATUS]
ELSE CONVERT(varchar(30),[IN(A_Date)],121)
END [IN(A_Date)]
,[OUT(A_Date)],[STATUS],[HOUR]
FROM CET3
ORDER BY D_Date,EnrollNumbeR


