Selecting entries between 2 sets of multiple keys.

Posted on 2009-07-02
Last Modified: 2012-05-07

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.

Question by:Richard Quadling
  • 3
LVL 40

Author Comment

by:Richard Quadling
ID: 24762208
Additional info. Periods will always start at period 1, but weeks may not be used for some contracts and can start at zero.

So, it 2 contracts start their financial year on April 1st 2009, then for the contract with weeks, Y2009P1W1 starts at the same time as Y2009P1W1 for a contract without weeks.

I think I've got some of the way.

I calculate a hash for the Y/P/W values.

Hash = ((Year * 14) + Period) * 6 + Week

13 periods actual periods and 0 = 14
5 weeks per period and 0 = 6

Using Excel as a test bed, this is working fine for the start Y/P/W

I now see that the To value needs to be careful for any 0 Period/Week.

The logic seems to be if Week is zero then the range is upto but not including the period+1, week 0 or year+1, period 0, week 0.


Start : Y2008 P7 W0 equates to 168714
End : Y2008 P0 W0 becomes Y2009 P0 W0 which equates to 168756

So the range is from >= 168714 and < 168756

But for an end of Y2008 P13 W5, this is <= 168755

And in Excel, this seems to be working.

(Hmmm, seems like my "quiet space" has arrived after all).

LVL 22

Accepted Solution

dportas earned 500 total points
ID: 24762247
I'm hoping that you have a Calendar table because such a query is very hard to optimise without (no sargable clauses). So try:

@StartDate =
 FROM Calendar
 WHERE YearNum >= @i_StartYear
 AND PeriodNum >= @i_StartPeriod
 AND WeekNum >= @i_StartWeek),
@EndDate =
 FROM Calendar
 WHERE YearNum <= @i_EndYear
 AND PeriodNum <= @i_EndPeriod
 AND WeekNum <= @i_EndWeek);

FROM YourTable
WHERE dt BETWEEN @StartDate AND @EndDate ;
LVL 40

Author Comment

by:Richard Quadling
ID: 24762518
That is a VERY good point.

We do have a "calendar" as such. The start dates for each period, so we know when to change the periods for outstanding orders.

Unfortunately, the period data is just a set of tags on an order. The date of the period change (and the subsequent date range for a from/to period range) have nothing to work on.

The issue is that an order can be re-entered and back dated (well, the Year/Period/Week are back dated), but all other dates on the record relate to the current datetime.

The YPW are JUST tags.

And that's why I'm having the problem I am.

Though the logic I've got so far SEEMS to be working quite nicely in Excel.

Just need to push it to SQL.
LVL 40

Author Comment

by:Richard Quadling
ID: 24763280
I think I've got it (well, testing seems to reveal the same answers as I expect).

The SP has the following parameters ...

      @s_Contract varchar(20),
      @i_StartYear integer,
      @i_StartPeriod integer,
      @i_StartWeek integer,
      @i_EndYear integer,
      @i_EndPeriod integer,
      @i_EndWeek integer

Working variables ...

      @b_EndRangeExactMatch bit,
      @i_EndValue integer,
      @i_PeriodsPerYear integer,
      @i_StartValue integer,
      @i_WeeksPerPeriod integer

Logic to get an inclusive range (see snippet)

Now I can do a where clause like this ...

            @s_Contract = POH.POH_CONTRACT
            (((POH.POH_CUST_YEAR * @i_PeriodsPerYear) + POH.POH_CUST_PERIOD) * @i_WeeksPerPeriod + POH.POH_CUST_WEEK) >= @i_StartValue
            (((POH.POH_CUST_YEAR * @i_PeriodsPerYear) + POH.POH_CUST_PERIOD) * @i_WeeksPerPeriod + POH.POH_CUST_WEEK) <= @i_EndValue

If you can see anything untoward, I'd like to know.
-- Assign defaults
	@b_EndRangeExactMatch = 1,
	@i_EndValue = 0,
	@i_PeriodsPerYear = 14,
	@i_StartValue = 0,
	@i_WeeksPerPeriod = 6
-- Set to zero all lesser elements if higher elements are zero.
IF (0 = @i_StartYear)
		@i_StartPeriod = 0
IF (0 = @i_StartPeriod)
		@i_StartWeek = 0
IF (0 = @i_EndYear)
		@i_EndPeriod = 0
IF (0 = @i_EndPeriod)
		@i_EndWeek = 0
-- If the EndYear, EndPeriod or EndWeek is 0 then the range is NOT an exact match.
-- Calculate the higher element when lower elements are zero.
IF (0 = @i_EndYear) -- No end year means the start of the next year where the year is this year.
		@b_EndRangeExactMatch = 0,
		@i_EndYear = 1 + Year(GetDate()),
		@i_EndPeriod = 0,
		@i_EndWeek = 0
ELSE IF (0 = @i_EndPeriod) -- No end period means the start the next year.
		@b_EndRangeExactMatch = 0,
		@i_EndYear = 1 + @i_EndYear,
		@i_EndWeek = 0
ELSE IF (0 = @i_EndWeek) -- No end week means the start of the next period.
		@b_EndRangeExactMatch = 0,
		@i_EndPeriod = 1 + @i_EndPeriod
-- Calculate the ranges.
	@i_StartValue = ((@i_StartYear * @i_PeriodsPerYear) + @i_StartPeriod) * @i_WeeksPerPeriod + @i_StartWeek,
	@i_EndValue = ((@i_EndYear * @i_PeriodsPerYear) + @i_EndPeriod) * @i_WeeksPerPeriod + @i_EndWeek
-- Convert inexact match to exact match.
IF (0 = @b_EndRangeExactMatch)
	-- If the range is such that they are the same value, then add 1 to the end of the range.
	IF (@i_StartValue = @i_EndValue)
			@i_EndValue = 1 + @i_EndValue
		@b_EndRangeExactMatch = 1,
		@i_EndValue = @i_EndValue - 1

Open in new window


Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A short article about problems I had with the new location API and permissions in Marshmallow
This is about my first experience with programming Arduino.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question