Cylex Cylex - 6 months ago 11
SQL Question

Checking the availability of the coach

Coach Table:

enter image description here

Tour Table:

enter image description here

Create a query which shows list of coach available for given dates

I have stuck on this query and i have no idea how to proceed:

SELECT
c.coachID,
c.coachNo
FROM
Tour t
inner join Coach c
on t.coachID=c.coachID
WHERE
'10/01/2016' BETWEEN startDate AND (DATEADD(DAY,tourDuration,startDate))


the output:

coachID|coachNo
1 |WKS5623

Answer

We can get the available coaches in following way

  1. Find out which coaches are in tour now

    SELECT DISTINCT CoachID FROM Tour WHERE '10/01/2016' BETWEEN startDate AND (DATEADD(DAY, tourDuration, startDate))

  2. Get those coaches who are not in the list of coaches are in tour now

    SELECT CoachId, CoachNo FROM Coach WHERE CoachId NOT IN ( SELECT DISTINCT CoachID FROM Tour WHERE '10/01/2016' BETWEEN startDate AND (DATEADD(DAY, tourDuration, startDate)) )

Hope It helps You.

Comments