我有一个表,它只使用月和日来定义几个不重叠的日期段。它基本上是一个库存项目的"库存"表,允许您为季节性业务全年储存不同数量的库存。下面是一个单个物品的库存表的外观示例。请注意,我有一个项目表,但我没有在这里显示。在下表中,ItemID是外键,StockID是主键。
StockID ItemID BeginDate EndDate QtyTrigger ReorderQty
1 1 11/1 1/31 0 1
2 1 2/1 5/31 1 2
3 1 6/1 10/31 1 1
因此,假设我想获得日期为2013年1月15日的再订购查询/报告。根据我当前的逻辑,我的查询将错误返回,因为它看起来像这样:
SELECT i.*, st.QtyTrigger, st.ReorderQty
FROM t20Itm AS i INNER JOIN t20Itm_Stock AS st ON i.ItemID = st.ItemID
WHERE #01/14/2013# >= CDate(st.BeginDate & "/2013" ) AND
#01/14/2013# <= CDate(st.EndDate & "/2013")
AND i.QtyOnHand <= st.QtyTrigger
问题是,本例中11/1的BeginDate需要为2012年11月1日。在我的例子中,我展示了2013,因为这是我能够想到的用于生成该查询的唯一逻辑。
我怀疑我的问题可能是一个更大的设计缺陷,所以我正在寻找如何实现这一点的答案,或者一个更好的想法,也许有一些原因说明这个设计是个坏主意。
我知道我可以通过不允许跨越12月31日的日期来实现这一点。然而,该应用程序的许多用户的时间段是从11月到2月,这是他们最慢的月份。他们最终会创建两个不同的日期跨度,其中包含相同的数据,一个是11月/12月,另一个是1月/2月。
我使用SQL Server作为数据库,使用MS Access作为前端应用程序。我可以使用T-SQL或Access VBA,因此您的解决方案可以是任意一种。
我相信这个肮脏的where子句破解应该做到:
WHERE
((CDate(st.BeginDate & "/2013" ) <= (CDate(st.EndDate & "/2013" ))
AND (#01/14/2013# BETWEEN CDate(st.BeginDate & "/2013" ) AND CDate(st.EndDate & "/2013")))
OR
((CDate(st.BeginDate & "/2013" ) > (CDate(st.EndDate & "/2013" ))
AND (#01/14/2013# BETWEEN CDate(st.BeginDate & "/2012" ) AND CDate(st.EndDate & "/2013")))
请注意,这两个OR’d表达式在年份上是不同的(2013年与2012年)。如果开始-结束是按时间顺序排列的,则使用第一个表达式(同年)。如果不是,则使用第二个表达式(从去年开始,以当前结束)。
然而,这可能会带来非常糟糕的性能。我建议您将日期存储为两个独立的列,并使用此逻辑或类似的逻辑进行查询。只有这样,你才能利用索引。