Friday, April 29, 2011

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

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