Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
-- 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
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.
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).