Akhter HUssain

Akhter HUssain

  • 681
  • 1.3k
  • 95.6k

How to use Join query between CTE and two table.?

Jan 5 2019 11:05 PM
I want to use a join query in CTE between two table EmployeeDetails Col(EmpID,EmpName) and second one is MachineAttendance Col(DateTime,INOUT), but getting confuse how it will use
getting error The multi-part identifier "cte1.EmpID" could not be bound.
 
;With CTE As
(
select ed.EmpID,ed.EmpName from EmployeeDetails ed
)
,cte1 AS
(
SELECT MA.EmpID, CAST(MA.Datetime as Date) AS [Date],
CASE WHEN MA.INOUT = 1 THEN DateTime END AS INOUT_INTIME,
CASE WHEN MA.INOUT = 2 THEN DateTime END AS INOUT_OUTTIME
From
MachineAttendance MA
),
cte2
as
(
select MA.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 MA.EmpID, [Date], INTIME, OUTTIME, [Hours]
, CASE WHEN [Hours] >= 8 THEN 1
WHEN [Hours] = 0 THEN 0
WHEN [Hours] >= 6 THEN 0.5 END AS [Day],
CASE WHEN [Hours] > 8 then [Hours] - 8 else 0 End as OT,
CASE WHEN [Hours] >= 8
then ([Hours] - 8) * 100 else 0 END AS OTAMount,
Convert(varchar(10),Date,120) as [Date],
Convert(varchar(10),INTIME,108) as [Time],
Case When Convert(Time,INTIME,108) > '09:10' Then 1 else 0 end as Late
from cte2
INNER Join cte On cte.EmpID=cte1.EmpID

Answers (1)