Solved

Selecting entries between 2 sets of multiple keys.

Posted on 2009-07-02
4
206 Views
Last Modified: 2012-05-07
Hi.

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

So,

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

etc.

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.


0
Comment
Question by:RQuadling
  • 3
4 Comments
 
LVL 40

Author Comment

by:RQuadling
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.

e.g.

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).

0
 
LVL 22

Accepted Solution

by:
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:

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

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

Author Comment

by:RQuadling
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.
0
 
LVL 40

Author Comment

by:RQuadling
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 ...

DECLARE
      @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 ...

      WHERE
            @s_Contract = POH.POH_CONTRACT
            AND
            (((POH.POH_CUST_YEAR * @i_PeriodsPerYear) + POH.POH_CUST_PERIOD) * @i_WeeksPerPeriod + POH.POH_CUST_WEEK) >= @i_StartValue
            AND
            (((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

SELECT

	@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)

	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
 

-- 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.

	SELECT

		@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.

	SELECT

		@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.

	SELECT

		@b_EndRangeExactMatch = 0,

		@i_EndPeriod = 1 + @i_EndPeriod
 

-- Calculate the ranges.

SELECT

	@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)

	BEGIN

	-- 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)

		SELECT

			@i_EndValue = 1 + @i_EndValue

	SELECT

		@b_EndRangeExactMatch = 1,

		@i_EndValue = @i_EndValue - 1

	END

Open in new window

0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
A short article about problems I had with the new location API and permissions in Marshmallow
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 …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now