Selecting entries between 2 sets of multiple keys.


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.

LVL 40
Richard QuadlingSenior Software DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Richard QuadlingSenior Software DeveloperAuthor Commented:
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).

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 ;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard QuadlingSenior Software DeveloperAuthor Commented:
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.
Richard QuadlingSenior Software DeveloperAuthor Commented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.