I have two table one is EmployeeDetails and another one is MachineAttendance,
now i want to use this for updating table INTIME and OUTTIME .
;WITH Dates (ReportingDate)
AS (
SELECT CONVERT(date, '2018-11-26 00:00:00.000', 120) AS ReportingDate
UNION ALL
SELECT DATEADD(day, 1, d.ReportingDate)
FROM Dates d
WHERE d.ReportingDate < CONVERT(date, '2018-12-25 00:00:00.000', 120))
,CTE As
(
select EmployeeDetails.EmpID,EmployeeDetails.EmpName,EmployeeDetails.OTEntitled,EmployeeDetails.Empcur,EmployeeDetails.Dhour,EmployeeDetails.LTime from EmployeeDetails where Empcur='Join'
)
,CTE4 As
(
Select MachineAttendance.EmpID,MachineAttendance.Datetime,MachineAttendance.INOUT from MachineAttendance
)
,cte1 AS
(
SELECT CTE4.EmpID, CAST(CTE4.Datetime as Date) AS [Date],
CASE WHEN CTE4.INOUT = 1 THEN CTE4.DateTime END AS INOUT_INTIME,
CASE WHEN CTE4.INOUT = 2 THEN CTE4.DateTime END AS INOUT_OUTTIME
From
CTE4
),
cte2
as
(
select cte1.EmpID, Date, MAX(INOUT_INTIME) AS INTIME,
MAX(INOUT_OUTTIME) AS OUTTIME
, DATEDIFF(Hour, MAX(INOUT_INTIME), MAX(INOUT_OUTTIME)) as [Hours]
FROM CTE1
GROUP BY EmpID, [Date]
)
select cte.EmpID,cte.EmpName,d.ReportingDate,cte2.Date, cte.OTEntitled,CTE.Empcur,CTE.Dhour,cte.LTime,cte2.INTIME, cte2.OUTTIME, cte2.[Hours]
, CASE WHEN cte2.[Hours] >= 8 THEN 1
WHEN cte2.[Hours] = 0 THEN 0
WHEN cte2.[Hours] >= 6 THEN 0.5 END AS [Day],
CASE WHEN cte2.[Hours] > CTE.Dhour then cte2.[Hours] - CTE.Dhour else 0 End as OT,
CASE when
cte.OTEntitled = 'Yes' AND cte2.[Hours] >= CTE.Dhour
THEN (( cte2.[Hours] - 8) * 100) else 0 END AS OTAMount,
Convert(varchar(10), cte2.INTIME,108) as [Time],
Case When Convert(Time, cte2.INTIME,108) > cte.LTime Then 1 else 0 end as Late
from cte Cross Apply Dates d
Left Join cte2 ON cte2.EmpId= cte.EmpID AND cte2.Date=d.ReportingDate
order by cte.EmpID asc
Blockquote
