I want to mark Sunday as Absent(A),when time does not come on Saturday and Monday ,then Mark Sunday as Absent ,other wise Mark Sunday as S ,if Saturday or Sunday time exit from each.
as you can see in image,Enrollnumber form (10001 to 10004) ,time does not exit on date 2021-06-05 and 2021-06-07,except EnrollNumber 10005 Time exit on 2021-06-07.
Then (10001 to 10004) sunday will be mark as a Absent(A) and 10005 sunday Mark as (S).
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)
,CTE3 AS (
SELECT *---,EnrollNumber,D_Date,Day,CONCAT(convert(varchar(30),[IN(A_Date)],120), CHAR(10),convert(varchar(30),[OUT(A_Date)],120)) [IN(A_Date)]
,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 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 D_Date,EnrollNumbeR





