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

Question

如果您能给我一些建议，告诉我管理预订最合适的方法，我将不胜感激。

我正在添加到一个现有的程序，用VB与MSSql数据库编写。

我目前使用win forms作为GUI，利用DataGridView来显示数据与一个月的控制。

我当前的数据库模式是:

bikeDetail (bikeID (PK), bikeName, bikeColour, bikeStyle, bikeNotes)
bikeMovements (bikeMovementID (PK), bikeID, customerID, bikeMovementType, bikeMovementDate, bikeMovementAMPM, bikeMovementNotes).
customer (customerID, ..., ...)

这个想法是，每当一辆自行车被运进或运出时，就会在bikeMovements数据库中进行记录。这可能不是保存这些数据最简单的方法，但它应该意味着数据库不会因为大量重复数据而变得臃肿。

我的问题是，我正在努力找出搜索bikemomovements表的最佳方法，以便找出自行车是否在或不在，因此可以预订。使用日历工具，我可以选择一个日期范围。

到目前为止，我已经能够通过使用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月22日起，自行车4号可用。

4月25日至29日有哪些自行车可用?应该是1号和4号自行车。抓自行车1很容易，自行车3是美中不足的，因为它在选定的日期范围内进出!

我认为最简单的方法是更改我的DB模式，以便为每天预订自行车创建记录。这将使搜索可用性变得容易得多，但会使表变得更加臃肿(当前每辆自行车租用2条记录，进出)，并且每天都有自行车租用的记录。公平地说，我们还不确定这些自行车能租多少天，这将影响我的决定。

任何建议将不胜感激!

Answer 1

如果我理解正确，那么如果在所需的日期范围内有运动记录(无论是out还是in)(它返回后的第二天可用)，则自行车不可用。如果最后一个移动记录不在

，也不可用。

查询

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小提琴