Parsnips.net

Today I had the unenviable task of trying to change an existing report which grouped line items into “aging bands”,  to have multiple aging bands.    The requirement given was  this:

The Dog Report currently has only one choice of Aging Band, which reports inventory in as follows:

0-180
181-270
271-330
331-365
366-455
>455

We need to amend this Aging Band filter by offering 3 choices:
1. “Standard” = This will be the current aging bands above as presented in the attachment
2. “Bank” = This will be 0-180, 181- 270, 271-365, >365
3. “Monthly” = This will be 0-30, 31-60, 61-90, 91-120, 121-150, 151-180, etc

The previous implementation logic would tag each record with a grouping label based on how many days from today the record was indicating.  Additionally, records in the future (DogDate < 0) would be grouped with 0 – 180… Here’s the old logic:

CREATE TABLE #TempAgingBands (
AgingBandId int,
Start_Value int,
End_Value int,
Label nvarchar(20)
)

CREATE TABLE #TempDog (
OrderDogDays varchar(2),
DogDate date
)

-- Setup Aging Band table
INSERT INTO #TempAgingBands (AgingBandId, Start_Value, End_Value, Label)
VALUES ('0', 0, 180, '0 - 180'),
	   ('1', 181, 270, '181 - 270'),
	   ('2', 271, 330, '271 - 330'),
	   ('3', 331, 365, '331 - 365'),
	   ('4', 366, 455, '365 - 455'),
	   ('5', 455, -1, '> 455')

--Some records are inserted into #TempDog

UPDATE #TempDog
SET OrderDogDays = (CASE WHEN DATEDIFF(day, DogDate, @Today) <= 180
						THEN 0
					WHEN DATEDIFF(day, DogDate, @Today) > 180 AND DATEDIFF(day, DogDate, @Today) <= 270
						THEN 1
					WHEN DATEDIFF(day, DogDate, @Today) > 270 AND DATEDIFF(day, DogDate, @Today) <= 330
						THEN 2
					WHEN DATEDIFF(day, DogDate, @Today) > 330 AND DATEDIFF(day, DogDate, @Today) <= 365
						THEN 3
					WHEN DATEDIFF(day, DogDate, @Today) > 365 AND DATEDIFF(day, DogDate, @Today) <= 455
						THEN 4
					WHEN DATEDIFF(day, DogDate, @Today) > 455
						THEN 5
					END)

The original developer didn’t make use of his Start_Value and End_Value fields he defined… I wanted to use something more flexible to encapsulate any aging band definition they came up with… So here’s my try:

UPDATE #TempDog
SET OrderDogDays = (SELECT t.AgingBandId FROM #TempAgingBands t WHERE t.End_Value = -1 AND DATEDIFF(DAY, DogDate, @Today) > t.Start_Value
					UNION
					SELECT TOP 1 t.AgingBandId FROM #TempAgingBands t WHERE DATEDIFF(day, DogDate, @Today) >= t.Start_Value
								AND DATEDIFF(day, DogDate, @Today) <= t.End_Value
								AND t.End_Value != -1 AND DATEDIFF(day, DogDate, @Today) >= 0
					UNION
					SELECT top 1 0 FROM #TempAgingBands t WHERE DATEDIFF(day, DogDate, @Today) < 0)

Can you think of a better way to do the third union?