如何从sql的两个日期获得免费的位置/场地 [英] How to get free location / venue from two dates in sql

问题描述

如果用户输入以下数据，我想获得免费和预订地点

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

推荐答案

首先更改数据库结构：有一个StartDate（DateTime），EndDate（DateTime）和一个位置（应该是一个不同表中的外键）。

目前，看起来你将这些存储为字符串，这是一个非常糟糕的想法，因为它使比较变得非常非常困难。您也不需要将星期几作为列存储，并且可以从日期开始非常容易地计算出来，并且对于两个相同的日期不能有所不同！

这也适用于周日预订开始但周一早上凌晨1点结束的情况。如果您不将结束时间存储为日期时间值，那么这种事情很难处理



那么您可以开始实际比较您的数据。我不能给你一个例子，因为问题太模糊了 - 你的数据样本没有明显的理由为什么当A和B被预订时C是免费的。



首先让你的数据结构正确，然后我们可以继续......

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...

这篇关于如何从sql的两个日期获得免费的位置/场地的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！