Use below query.
SQL
SELECT CONVERT(CHAR(3), 'Monday', 0) Day -- Mon
SELECT CONVERT(CHAR(3), 'Tuesday', 0) Day -- Tue
Use below query.
;with cte as (
select a.EnrollNumber,a.EmpName,a.FatherName,b.Date,CONVERT(CHAR(3), b.Day, 0) Day,c.A_Date ,a.Duty_Hours,a.INTIME
from EmpMaster a
cross join MonthDate b
left join AttendenceLOG c
on a.EnrollNumber=c.EnrollNumber
and cast(c.A_Date as date)=b.Date)
,cte1 AS (
SELECT EnrollNumbeR,EmpName,FatherName,Date,CONVERT(CHAR(3), Day, 0) Day,Duty_Hours,INTIME
,MIN(A_Date) [IN(A_Date)],MAX(A_Date) [OUT(A_Date)]
FROM CTE
GROUP BY EnrollNumbeR,EmpName,FatherName,Date,CONVERT(CHAR(3), Day, 0) Day,Duty_Hours,INTIME)
,CTE2 AS (
SELECT EnrollNumbeR,EmpName,FatherName,Date,CONVERT(CHAR(3), Day, 0) Day,Duty_Hours,INTIME
,CASE WHEN [IN(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([IN(A_Date)] AS DATE) AS DATETIME)) THEN NULL ELSE [IN(A_Date)] END TimeIN
,CASE WHEN [OUT(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([OUT(A_Date)] AS DATE) AS DATETIME)) THEN [OUT(A_Date)] ELSE NULL END TimeOut
FROM cte1)
SELECT *
,CASE
WHEN [DAY] ='Sun' and TimeIN IS NULL AND TimeOut IS NULL THEN 'S'
-- when Duty_Hours>=8 then 'P'
WHEN DATEDIFF(HOUR,TimeIN,TimeOut)>=Duty_Hours THEN 'P'
ELSE 'A' END STATUS,
Case
when Day ='Sun' then 1
WHEN DATEDIFF(HOUR,TimeIN,TimeOut)>=Duty_Hours THEN 1 else 0
END
Duty,
CASE WHEN TimeIN IS NOT NULL AND TimeOut IS NOT NULL AND DATEDIFF(HOUR,TimeIN,TimeOut)>=8
THEN CAST(CAST(DATEDIFF(MINUTE,TimeIN,TimeOut) AS FLOAT)/60 AS decimal(10,2)) ELSE 0 END [HOUR],
Case when DATEDIFF(MINUTE,INTIME,TimeIN)>15 Then 1 else 0 END Late
,DATEDIFF(MINUTE,INTIMe,Convert(TIme(0),TimeIN)) LateM
FROM CTE2
where date between @DateFrom and @DateTo
ORDER BY EnrollNumbeR,Date
