Error Getting
Msg 241, Level 16, State 1, Line 25
Conversion failed when converting date and/or time from character string.
When I am mentioned 'A'
,CASE WHEN [IN(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([IN(A_Date)] AS DATE) AS DATETIME)) THEN 'A' ELSE [IN(A_Date)] END [IN(A_Date)]
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 'A' 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)
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
ORDER BY D_Date,EnrollNumbeR

