TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Akhter HUssain
720
1.3k
101.3k
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
Reply
Answers (
1
)
I need to select rows
Create a procedure to insert values from a single table