I am building a booking app and part of this involves reserving a courtesy car.

Courtesy cars can be booked out for a period, e.g.

2nd April - 11 April 12 April - 19 April etc...

These dates are stored in a courtesycarbooking table.

What I want to write is a query which can query the courtesycar and courtesycarbooking tables (they are related) for free dates. Free dates are those which are NOT in the booking table.

The user will specify, in a javascript widget, a start and end date for the courtesy car, which will be sent to the sql server.

How could/should a query like this be written?

  • 1
    Learn something. Try something. If you can't even attempt to create the query, how can you create the app???
    – Eric
    Feb 6 '20 at 23:07
  • Please consider reading this advice
    – mustaccio
    Feb 7 '20 at 3:34

To do what you want first step is generate a list of Dates then join that reservation table to the generate list of dates.

Here is a thread on how to Generate list of Dates in MSSQL

From this result set select where List of dates table failed to joined to the reservation table. The reservation columns will be filled with NULLs

To write a sample query for you, have to know the structure the table and unique keys used to identify the records.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.