How to get free location / venue from two dates in sql

Question

0.00/5 (No votes)

See more:

If user inputs following data, I want to get free and Booked Locations

Start Date : 01/03/2015

End Date : 31/03/2015

Start Time : 02:30 PM

End Time : 05:30 PM

I have Following table structure for storing locations date wise

Date         Day          StartTime  EndTime   Location
2015-03-01   Sunday       2:30 PM    05:30 PM  Location - A
2015-03-02   Monday       4:30 PM    05:30 PM  Location - B
2015-03-03   Tuesday      5:45 PM    06:45 PM  Location - C

output like :

Location - A Booked

Location - B Booked

Location - C Free

Posted 30-Mar-15 21:49pm

Yagnik_Vishal

Updated 30-Mar-15 22:12pm

Afzaal Ahmad Zeeshan

v3

Add a Solution

Comments

Afzaal Ahmad Zeeshan 31-Mar-15 4:13am

You might consider using a database and third-party API to determine such values and then present them on the screen to the user. What do you have at the moment, just the idea?

Sergey Alexandrovich Kryukov 31-Mar-15 5:31am

"There is no such thing as free location". :-)
—SA

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-03-30T22:47:00

Start by changing your DB structure: Have a StartDate (DateTime), EndDate (DateTime), and a Location (which should probably be a foreign key into a different table).
At the moment, it looks like you are storing these as strings, which is a very poor idea as it makes comparison very, very difficult. You also don't need to store the day of the week as a column, and that can be worked out very easily from the date and can't be different for two identical dates!
This also allows for occasions when the booking starts on Sunday but ends on Monday morning at 1AM. That kind of thing is very awkward to cope with if you don't store end times as datetime values

Then you can start actually comparing your data. I can't give you an example as the question is too vague - there is no obvious reason from your data sample why "C" is free when "A" and "B" are booked.

So start by getting your data structures right, and then we can move on...