If you put your original query into a Common Table Expression (CTE) you can easily join it to your ShiftTable e.g.
;WITH CTE AS
(
Select EmpShift.EmpID,EmpShift.ShiftName,EmpShift.StartDate,EmpShift.EndDate,
CONVERT(VARCHAR(10), InOut.inout, 112) AS WORKDATE, convert(char(5), InOut.inout, 108) As CheckTime,
InOut.CheckType
From EmpShift
Inner Join InOut On
EmpShift.EmpID=InOut.EmpID
Where (StartDate Between '2015-05-01' AND '2015-06-10') AND (EndDate Between '2015-05-01' AND '2015-06-10')
GROUP BY EmpShift.EmpID, CONVERT(VARCHAR(10),InOut.inout, 112),convert(char(5), InOut.inout, 108),EmpShift.EmpID,EmpShift.ShiftName,
EmpShift.StartDate,EmpShift.EndDate,InOut.CheckType
)
select *
from CTE
INNER JOIN ShiftTable ST ON CTE.ShiftName = ST.ShiftName
which will add the ShiftTable
StartTime
and
EndTime
to your results above.
All you need to do then is add a comparison to the appropriate column in your select e.g.
CASE WHEN CTE.CheckType = 'O' THEN
ABS(DATEDIFF(MINUTE, CTE.CheckTime, ST.EndTime))
ELSE
ABS(DATEDIFF(MINUTE, CTE.CheckTime, ST.StartTime))
END as TimeDiff,
CASE WHEN CTE.CheckType = 'O' THEN
CASE WHEN CTE.CheckTime > ST.EndTime THEN 'Worked Late'
WHEN CTE.CheckTime < ST.EndTime THEN 'Left Early'
ELSE 'On Time'
END
ELSE
CASE WHEN CTE.CheckTime > ST.StartTime THEN 'Arrived Late'
WHEN CTE.CheckTime < ST.StartTime THEN 'Started Early'
ELSE 'On Time'
END
END As EarlyLate