I've got a table that defines several non-overlapping date periods using only month and day. It's basically a "stocking" table for inventory items that allows you to stock different amounts across the year for seasonal businesses. Here's an example of how a single item's stocking table might look. Note that I have an Items table that I'm not showing here. In this table below ItemID is a Foreign Key, StockID is the Primary Key.
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
So let's say I want to get a Reorder Query/Report for the date January 15, 2013. With my current logic my query would return incorrectly because it would look like this:
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
The problem is that the BeginDate of 11/1 in this example needs to be 11/1/2012. In my example I'm showing 2013 because that's the only logic I've been able to come up with to use to generate this query.
I suspect my problem here might be a bigger design flaw so I'm looking for an answer of how to accomplish this or else a better idea, maybe with some reasons why this design is a bad idea.
I know I could accomplish this by not allowing date spans that go across December 31st. However, a lot of the users of this application have a time period from November to February that is their slowest months. They'll end up creating two different date spans with the same data in them, one for Nov/Dec and another for Jan/Feb.
I'm using SQL Server for the database and MS Access for the front end application. I can use T-SQL or Access VBA, so your solution can be in either one.
I believe this filthy-where-clause-hack should do it:
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")))
Pay attention that these two OR'd expressions are different in year (2013 vs. 2012). If begin-end is in chronological order, first expression is used (same year). If not, second expression is used (begins with last year, ends with current).
This however can have really poor performance. I suggest you store your dates as two separate columns and use this or similar logic to query. Only then you can take advantage of indexes.