I am converting an Excel based forecasting workbook into a SQL stored procedure. I am having difficulty converting 2 dependent Excel tables because they use a prior rolling time period value to calculate future weeks. I'm not sure how to reference a prior week when the prior week relies on its prior week and so on...
Here's a sample workbook on Dropbox: https://www.dropbox.com/s/efeljfea9kkcjhb/SQL%20Trend%20Factor.xlsm
And some code to create a temp table that is similar to my actual table structure:
CREATE TABLE #test
(entity char(1) not null,
period tinyint not null,
value smallint not null
)
INSERT INTO #test (entity,period,value)
VALUES('A',1,500),
('A',2,600),
('A',3,700),
('A',4,650),
('A',5,675),
('B',1,400),
('B',2,450),
('B',3,425),
('B',4,500),
('B',5,600),
('C',1,500),
('C',2,550),
('C',3,525),
('C',4,535),
('C',5,550)
SELECT * FROM #test
Thank you in advance for any help you are able to provide. I've been able to find many other solutions on this site, and appreciate the help you all provide!
