SQLTeam.com | Weblogs | Forums

How to update table using CTE query in a procedure

sql2008r2

#1

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


#2

hi

hope this helps

:slight_smile:
:slight_smile:

https://social.technet.microsoft.com/Forums/en-US/2ae014d2-9c52-4a51-9db7-f83885828142/how-to-use-cte-in-update-statement?forum=transactsql