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?