CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
MartGriff
MartGriff

Reputation: 2851

Time Slot SQL Query

I'm trying to write a query to see if an engineer visited his job in a agreed time slot.

Table Screenshot http://img24.imageshack.us/img24/1156/capturebcn.jpg

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.

Upvotes: 0

Views: 1377

Answers (2)

bortzmeyer
bortzmeyer

Reputation: 35479

Why not simply:

WHERE Startdate = BookedDate AND StartTime <= TimeSlotEnd AND StartTime >= TimeSlotStart

?

Upvotes: 0

gbn
gbn

Reputation: 432331

SELECT
    CASE
        WHEN StartDate = BookedDate AND StartTime BETWEEN TimeSlotStart and TimeSlotEnd
            THEN 'True'
        ELSE 'False'
    END
FROM
    ...

Upvotes: 5

Related Questions