I'm trying to write a query to see if an engineer visited his job in a agreed time slot.
This is my query so far:
SELECT
v.[VISITDATE],
CONVERT(VARCHAR, v.[STARTTIME], 105) AS 'Startdate',
CONVERT(VARCHAR, v.[STARTTIME], 108) AS 'StartTime',
CONVERT(VARCHAR, v.[bookeddate], 105) AS 'BookedDate',
CONVERT(VARCHAR, t.[starttime], 108) AS 'TimeSlotStart',
CONVERT(VARCHAR, t.[endtime], 108) AS 'TimeSlotEnd',
v.[Status]
FROM
[tbl_repair_visit] v
INNER JOIN [ltbl_TimeSlots] t ON v.timeslot = t.[Slot]
The 'StartDate'
and 'StartTime'
is the date and time the engineer went.
'BookedDate'
is the date he should have gone, and 'TimeSlotStart'
and 'TimeSlotEnd'
defines the time span in which he should have started working. So I need a column that is a True/False value to say if he went in the correct time or not.
From stackoverflow
-
SELECT CASE WHEN StartDate = BookedDate AND StartTime BETWEEN TimeSlotStart and TimeSlotEnd THEN 'True' ELSE 'False' END FROM ...
-
Why not simply:
WHERE Startdate = BookedDate AND StartTime <= TimeSlotEnd AND StartTime >= TimeSlotStart
?
0 comments:
Post a Comment