Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

Selecting entries between 2 sets of multiple keys.

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
Richard Quadling
Asked:
Richard Quadling
  • 3
1 Solution
 
Richard QuadlingSenior Software DeverloperAuthor 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.

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
 
dportasCommented:
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
 
Richard QuadlingSenior Software DeverloperAuthor 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.
0
 
Richard QuadlingSenior Software DeverloperAuthor 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 ...

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now