asked
Adding Dates to Recursive CTE
I have a recursive CTE that calculates projected profit for the duration of a project. I'm trying to figure out to add a corresponding month/date to each iteration/period. My CTE is as follows. No project can exceed 48 months in length, hence my max recursion value of 48. So if a project started on January 2, 2017, and is 37 months in duration, I'd like to associate a month for each period. DECLARE @OrigAmt MONEY DECLARE @Duration INT SET @OrigAmt = 49673330 SET @Duration = 37; WITH CTE AS ( SELECT [Duration], [Remaining Periods], Amt = CAST([Forecast Rate] * @OrigAmt AS MONEY), Balance = CAST(@OrigAmt - (@OrigAmt * [Forecast Rate]) AS MONEY) FROM DataWarehouse.dbo.dimForecast_Rates WHERE [Duration] = @Duration AND [Remaining Periods] = 1 UNION ALL SELECT b.Duration, b.[Remaining Periods], Amt = CAST(b.[Forecast Rate] * CTE.Balance AS MONEY), Balance = CAST(CTE.Balance - (CTE.Balance * b.[Forecast Rate]) AS MONEY) FROM CTE JOIN DataWarehouse.dbo.dimForecast_Rates b ON b.Duration = CTE.Duration AND b.[Remaining Periods] = CTE.[Remaining Periods] + 1 ) SELECT Duration, [Remaining Periods], Amt , Balance FROM CTE ORDER BY [Remaining Periods] OPTION (MAXRECURSION 48)
Not sure where the date is coming from, but here's an example using a variable that could be replaced by a column from a table. DECLARE @OrigAmt MONEY, @Duration INT, @StartDate date SELECT @OrigAmt = 49673330, @Duration = 37, @StartDate = '20170102'; WITH CTE AS ( SELECT [Duration], [Remaining Periods], Amt = CAST([Forecast Rate] * @OrigAmt AS MONEY), Balance = CAST(@OrigAmt - (@OrigAmt * [Forecast Rate]) AS MONEY), Date = @StartDate FROM dbo.dimForecast_Rates WHERE [Duration] = @Duration AND [Remaining Periods] = 1 UNION ALL SELECT b.Duration, b.[Remaining Periods], Amt = CAST(b.[Forecast Rate] * CTE.Balance AS MONEY), Balance = CAST(CTE.Balance - (CTE.Balance * b.[Forecast Rate]) AS MONEY), Date = DATEADD( MM, 1, CTE.Date) FROM CTE JOIN dbo.dimForecast_Rates b ON b.Duration = CTE.Duration AND b.[Remaining Periods] = CTE.[Remaining Periods] + 1 ) SELECT Duration, [Remaining Periods], Amt , Balance, Date FROM CTE ORDER BY [Remaining Periods] OPTION (MAXRECURSION 48)
I came up with the same answer: DECLARE @OrigAmt MONEY DECLARE @Duration INT DECLARE @startDate DATE SET @OrigAmt = 49673330 SET @Duration = 37 SET @startDate = '2014-10-01'; WITH CTE AS ( SELECT [Duration] ,[Remaining Periods] ,@startDate AS [Mnth] ,Amt = CAST([Forecast Rate] * @OrigAmt AS MONEY) ,Balance = CAST(@OrigAmt - (@OrigAmt * [Forecast Rate]) AS MONEY) FROM APDataWarehouse.dbo.dimForecast_Rates WHERE [Duration] = @Duration AND [Remaining Periods] = 1 UNION ALL SELECT b.Duration ,b.[Remaining Periods] ,DATEADD(MONTH, 1, Mnth) AS Mnth ,Amt = CAST(b.[Forecast Rate] * CTE.Balance AS MONEY), Balance = CAST(CTE.Balance - (CTE.Balance * b.[Forecast Rate]) AS MONEY) FROM CTE JOIN APDataWarehouse.dbo.dimForecast_Rates b ON b.Duration = CTE.Duration AND b.[Remaining Periods] = CTE.[Remaining Periods] + 1 ) SELECT Duration ,[Remaining Periods] ,Mnth Amt , Balance FROM CTE ORDER BY [Remaining Periods] OPTION (MAXRECURSION 48)
19 People are following this question.
