Parsnips.net

So in yesterdays post, the “monthly” requirement was going to be a challenge. Let’s remind ourselves of what the requirement is:

3. “Monthly” = This will be 0-30, 31-60, 61-90, 91-120, 121-150, 151-180, etc

The problem is, I don’t know how many of those groupings can exist. To solve this, I went into the bag of tricks and pulled out some recursion.

--SETS up aging bands in 30 day increments.
-- 0-30, 31-60, 61-90 until all inventory is accounted for.

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

CREATE TABLE #TempAgingBandsEndValue (
AgingBandId int identity(0,1),
End_Value  int
)

declare @Today date
declare @DogDate date
declare @MaxDays int

-- Get two dates, today and say some time in 1900's to show whats up
set @TODAY = GETDATE()
set @DOGDATE = DATEADD(DAY, -40150, @Today)
set @MaxDays = DATEDIFF(day, @DogDate, @Today)  ;   

-- Use Recursion to generate a list of start values (1, 31, 61, 91 etc) *NOTE You'll see how we'll change 1 to 0 below*
WITH Nbrs ( n ) AS (
		SELECT 1 UNION ALL
		SELECT 30 + n FROM Nbrs WHERE n < @maxDays )
	INSERT INTO #TempAgingBands (Start_Value)
	SELECT n FROM Nbrs
	OPTION ( MAXRECURSION 32767 ) ;

-- Use recursion to generate list of end values (30, 60, 90, 120 etc)
WITH Nbrs ( n ) AS (
        SELECT 30 UNION ALL
        SELECT 30 + n FROM Nbrs WHERE n < @maxDays + 30 )
    INSERT INTO #TempAgingBandsEndValue(End_Value)
	SELECT n FROM Nbrs
    OPTION ( MAXRECURSION 32767 ) ;

-- Put start and end values in the aging band table.
UPDATE #TempAgingBands
SET #TempAgingBands.End_Value = t2.End_Value
FROM #TempAgingBandsEndValue t2
WHERE #TEmpAgingBands.AgingBandId = t2.AgingBandId

-- The first record is 1,30 it should be 0,30 so update it.
UPDATE #TempAgingBands
SET Start_Value = 0
WHERE AgingBandId = 0

-- Update to get appropriate list of labels.
UPDATE #TempAgingBands
SET Label = (convert(varchar(7),Start_Value) + ' - ' + convert(varchar(7),End_Value))

-- See the results. #winning
select * from #TempAgingBands

Is there a way to update #TempAgingBands without creating a second temp table?