关于用VB在MSSql中搜索可用性的建议

Question

我希望得到一些关于管理预订的最合适方法的建议。

我正在添加一个现有的程序，用VB写的一个MSSql DB。

我目前使用win forms作为图形用户界面，使用DataGridView来显示带有Month控件的数据。

我当前的DB模式是：

bikeDetail (bikeID (PK), bikeName, bikeColour, bikeStyle, bikeNotes)

bikeMovements (bikeMovementID (PK), bikeID, customerID, bikeMovementType, bikeMovementDate, bikeMovementAMPM, bikeMovementNotes).

customer (customerID, ..., ...)

其想法是，每次自行车进出时，都会在bikeMovements数据库中创建一条记录。这可能不是保存这些数据的最简单的方法，但它应该意味着数据库不会因大量重复数据而变得臃肿。

我的问题是，我正在努力找出通过bikeMovements表进行搜索的最佳方式，以便找出自行车是进还是出，因此可以进行预订。使用日历工具，我可以选择日期范围。

到目前为止，我已经能够通过使用sql查询来计算自行车在某个日期是否可用，但不能用于日期范围。

当前sql查询：

    SELECT * FROM tb_bikeMovements as bm, tb_bikeDetail as bd
    WHERE bm.bikeMovementDate < '" + startDate + "'
    and bm.bikeID = bd.bikeID
    and bm.bikeMovementType = 1 

这将返回从那一天起可用的自行车。(bikeMovementType 1是返回的自行车，因此是可用的)。

我非常感谢任何关于更好的sql查询来恢复可用性的建议，或者更好的设置数据库的方法，使查询变得更简单。

示例日期：

自行车1于4月22日发车，4月24日回售(4月25日起提供)

自行车2于4月22日发车，4月28日回售(从4月29日开始发售)

自行车3将于4月26日发车，并在4月27日重新预订(4月28日起提供)

自行车4从4月22日起可供使用。

4月25日至29日有哪些自行车？(应该是自行车1和自行车4。赶上自行车1很容易，自行车3是美中不足的，因为它在选定的日期范围内进出！)

我认为最简单的方法是更改我的数据库模式，以便为预订自行车的每一天创建一条记录。这将使搜索可用性变得容易得多，但也会使表格变得更加臃肿(目前每个自行车租用的2条记录，进出)，以及自行车每天离开的记录。公平地说，我们还不确定这些自行车将被租赁多少天，这将对我的决定产生影响。

我们将非常感谢您的建议！

Answer 1

如果我理解正确的话，那么自行车是不可用的，如果在所需的日期范围内有移动记录(无论是出还是入)(它在退还后的第二天是可用的)，如果最后一个移动记录是出的，它也是不可用的

查询

select distinct bikeid from bikemovements where bikeMovementDate between '25 April 2013' and '29 April 2013'

会给那些在4月25日到29日间搬进来或搬出去的自行车

因此我们可以通过使用"not in“来查找可用的自行车来否定这一点。

select * from bikeDetail where bikeid not in (
  select distinct bikeid from bikemovements where bikeMovementDate between '25 April 2013' and '29 April 2013'
) 

但是我想我们也应该检查一辆自行车是不是已经被预订出去了，还没有进来。

这将获得每辆自行车的最后一个运动日(直到开始日期)

  select bikeid bid, max(bikemovementdate) bmd from bikemovements
  where bikemovementdate < '25 April 2013' 
  group by bikeid) lastmove 

为了查看运动记录，我们可以

select * from bikemovements join 
(
  select bikeid, max(bikemovementdate) bmd from bikemovements
  where bikemovementdate < '25 April 2013' 
  group by bikeid
) lastmove 
on lastmove.bikeid=bikemovements.bikeid and 
   lastmove.bmd=bikemovements.bikemovementdate

仅查找具有最后一个out移动的那些

select bikemovements.bikeid from bikemovements join 
(
  select bikeid, max(bikemovementdate) bmd from bikemovements
  where bikemovementdate < '25 April 2013' 
  group by bikeid) lastmove 
on lastmove.bikeid=bikemovements.bikeid and 
   lastmove.bmd=bikemovements.bikemovementdate
where bikeMovementType = 'out'

所以把所有这些放在一起，我们可以得到一些类似的东西

select * from bikeDetail where bikeid not in (
select distinct bikeid from bikemovements 
  where bikeMovementDate between '25 April 2013' and '28 April 2013'
  ) 
and bikeid not in ( 

select bikemovements.bikeid from bikemovements join 
(
  select bikeid, max(bikemovementdate) bmd from bikemovements 
  where bikemovementdate < '25 April 2013' 
  group by bikeid
) lastmove 
 on lastmove.bikeid=bikemovements.bikeid and 
   lastmove.bmd=bikemovements.bikemovementdate
 where bikeMovementType = 'out'
)

sql fiddle