Selecting entries between 2 sets of multiple keys.
Posted on 2009-07-02
Ultimately, this is going to end up in an SQL query, but I'm struggling. Probably because I'm in a rush, under pressure and not having the "quiet space" I need at the moment.
So. The problem.
There are transactions. They have 3 significant columns : Year, Period and Week.
I need to select all transactions from a Start Year/Period/Week to an End Year/Period/Week.
If any element (Year, Period, Week) is 0, then the remainder of the selections are also zero. E.g. A zero Period forces a zero Week. A zero Year forces a zero Period AND a zero Week.
The "zero"-ing above only affects the from or the to. Selecting a From Year 0 does not mean an End Year 0.
A zero value means the first or last of the element (see some examples below).
NOTE: Period is NOT calendar month. So Y2009, P1 could be ANY month. If the contract starts its financial year in October, then Y2009P10 is actually October 2010. The Y/P/W and when to allocate them is supplied by the contract, not controlled by us. The data does NOT have the financial year start available, just the contracts Y/P/W for analysis
Y2009 P1 W4 to Y2009 P2 W1 From the Week 4 of Period 1 to the Week 1 of Period 2 in the Year 2009.
Y2009 P0 W0 to Y2009 P2 W0 From the start of 2009 to the end of Period 2 in the Year 2009.
Y2008 P7 W0 to Y2008 P0 W0 Essentially the second half of 2008.
Y0 P1 W2 is invalid and would be thought of as Y0 P0 W0
Any combination could be asked for.
There could be 13 periods per year.
There could be 5 weeks per period.
With regard to the zeroing of start/end elements, I'm handling that in SQL as ...
-- If Year/Period is 0, then Period/Week become 0
IF (0 = @i_StartYear) SELECT @i_StartPeriod = 0
IF (0 = @i_StartPeriod) SELECT @i_StartWeek = 0
IF (0 = @i_EndYear) SELECT @i_EndPeriod = 0
IF (0 = @i_EndPeriod) SELECT @i_EndWeek = 0
What I'm looking for is a big IF statement which covers all these rules.