SQLTeam.com | Weblogs | Forums

Updating Pivoted table from a flat source with a twist


#1

Hi, Sorry for what might be a newbie question in a advance.
I have a user submitted file for financial forecast by project and general ledger account by month for up to 15 years out that gets loaded into a flat staging table.

--Flat Staging Table with user submitted forecast

CREATE TABLE dbo.[STG_fcst](
[PROJ_ID] nvarchar NOT NULL,
[ACT_ID] nvarchar NOT NULL,
[FC_DATE] nvarchar NOT NULL,
[AMT_USD] [numeric](20, 2) NULL,
[UPDATEID] nvarchar NULL,
[DTSTAMP] [datetime] NULL

Only one column for Dates. Users just provide year and month. On the other end I have a pivoted table that is segregated by forecast folder that looks like this

CREATE TABLE dbo.[FORECAST](
[PLANKEY] varchar NOT NULL,--Plankey=Folderkey+project_id
[PROJ_ID] varchar NOT NULL,
[ACT_ID] varchar NOT NULL,
[Y1_ACT_JAN] [numeric](20, 2) NULL,
[Y1_ACT_FEB] [numeric](20, 2) NULL,
[Y1_ACT_MAR] [numeric](20, 2) NULL,
[Y1_ACT_APR] [numeric](20, 2) NULL,
[Y1_ACT_MAY] [numeric](20, 2) NULL,
[Y1_ACT_JUN] [numeric](20, 2) NULL,
[Y1_ACT_JUL] [numeric](20, 2) NULL,
[Y1_ACT_AUG] [numeric](20, 2) NULL,
[Y1_ACT_SEP] [numeric](20, 2) NULL,
[Y1_ACT_OCT] [numeric](20, 2) NULL,
[Y1_ACT_NOV] [numeric](20, 2) NULL,
[Y1_ACT_DEC] [numeric](20, 2) NULL,
[Y1_FC_JAN] [numeric](20, 2) NULL,
[Y1_FC_FEB] [numeric](20, 2) NULL,
[Y1_FC_MAR] [numeric](20, 2) NULL,
[Y1_FC_APR] [numeric](20, 2) NULL,
[Y1_FC_MAY] [numeric](20, 2) NULL,
[Y1_FC_JUN] [numeric](20, 2) NULL,
[Y1_FC_JUL] [numeric](20, 2) NULL,
[Y1_FC_AUG] [numeric](20, 2) NULL,
[Y1_FC_SEP] [numeric](20, 2) NULL,
[Y1_FC_OCT] [numeric](20, 2) NULL,
[Y1_FC_NOV] [numeric](20, 2) NULL,
[Y1_FC_DEC] [numeric](20, 2) NULL,
[Y2_FC_JAN] [numeric](20, 2) NULL,
[Y2_FC_FEB] [numeric](20, 2) NULL,
[Y2_FC_MAR] [numeric](20, 2) NULL,
[Y2_FC_APR] [numeric](20, 2) NULL,
[Y2_FC_MAY] [numeric](20, 2) NULL,
[Y2_FC_JUN] [numeric](20, 2) NULL,
[Y2_FC_JUL] [numeric](20, 2) NULL,
[Y2_FC_AUG] [numeric](20, 2) NULL,
[Y2_FC_SEP] [numeric](20, 2) NULL,
[Y2_FC_OCT] [numeric](20, 2) NULL,
[Y2_FC_NOV] [numeric](20, 2) NULL,
[Y2_FC_DEC] [numeric](20, 2) NULL,
[Y3_FC] [numeric](20, 2) NULL,
[Y4_FC] [numeric](20, 2) NULL,
[Y5_FC] [numeric](20, 2) NULL,
[Y6_FC] [numeric](20, 2) NULL,
[Y7_FC] [numeric](20, 2) NULL,
[Y8_FC] [numeric](20, 2) NULL,
[Y9_FC] [numeric](20, 2) NULL,
[Y10_FC] [numeric](20, 2) NULL,
[Y11_FC] [numeric](20, 2) NULL,
[Y12_FC] [numeric](20, 2) NULL,
[Y13_FC] [numeric](20, 2) NULL,
[Y14_FC] [numeric](20, 2) NULL,
[Y15_FC] [numeric](20, 2) NULL,
[UPDATEID] varchar NOT NULL,
[DTSTAMP] [datetime] NOT NULL,
CONSTRAINT [PK_FORECAST] PRIMARY KEY CLUSTERED
(
[PLANKEY] ASC,
[PROJ_ID] ASC,
[ACT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

It is a 15 year forecast segregated by plan_key which denotes basically forecast folder(i.e. Jan Fcst, May fcst etc.)
First two calendar years for each forecast folder is by month years 3 to 15 have to be aggregated by year. There are no dates just columns for Y1_Jan,Y1_Feb etc. What year it is is determined by the folder settings.

I also have a table that tells me for each folder what the first forecasted month and year should be and a counter of how many month need to be by month so if the first forecasted month is Feb then counter is 24 if March then 23.

--Folder Setting table

CREATE table dbo.[FOLDER_FCST_DT_XREF_VW]
(FOLDERKEY varchar(255),
(FOLDERNAME VARCHAR(255),
FCST_BASE_DATE DATE,
CTR_STOP INT)

So the question is this how I would take data in a staging table and load it into forecast table that is pivoted and aggregate years 3 to 15. Here is some sample data for the staging and the folder tables. Thank you very much for your help.

--Data for Folder Table
insert into dbo.[FOLDER_FCST_DT_XREF_VW]
(FOLDERKEY,FOLDERNAME,FCST_BASE_DATE,CTR_STOP)
Values
('AA99','Study Scenario Estimates (2016)',2016-06-01,'19'),
('AB05','2015 Feb Refresh',2015-02-01,'23'),
('AB06','2015 March Refresh',2015-03-01,'22'),
('AB07','2015 May Forecast',2015-04-01,'21'),
('AB08','2015 May Refresh',2015-05-01,'20'),
('AB09','2015 Jun Refresh',2015-06-01,'19'),
('AB10','2015 Aug Forecast',2015-07-01,'18'),
('AB11','2015 Aug Refresh',2015-08-01,'17'),
('AB12','2015 Sept Refresh',2015-09-01,'16'),
('AB13','2015 Nov Forecast',2015-10-01,'15'),
('AB14','2015 Dec Refresh',2015-12-01,'13'),
('AB16','2015 Actuals',2016-01-01,'24'),
('AB17','2016 Feb Tally',2016-02-01,'23'),
('AB18','2016 Feb Refresh',2016-02-01,'23'),
('AB19','2016 March Refresh',2016-03-01,'22'),
('AB20','2016 May Forecast',2016-04-01,'21'),
('AB21','2016 June Refresh',2016-06-01,'19'),
('AB22','2016 Aug Forecast',2016-07-01,'18'),
('AB23','2016 Sep Refresh',2016-09-01,'16'),
('AB24','2016 Nov Forecast',2016-10-01,'15'),
('AB25','2016 Dec Refresh',2016-11-01,'14')


--Data for Staging Table
insert into dbo.STG_fcst
values

('Project51','G1000','2017.03',150),
('Project7','G1000','2017.03',1867726.45),
('Project8','G1000','2017.03',168827.4),
('Project10','G1000','2017.03',38070.41),
('Project32','G1000','2017.03',13574.25),
('Project44','G1000','2017.03',23405.95),
('Project45','G1000','2017.03',164787.89),
('Project55','G1000','2017.03',76364),
('Project50','G1000','2017.03',16714.98),
('Project59','G1000','2017.03',104135.17),
('Project74','G1000','2017.03',65706),
('Project73','G1000','2017.03',5775),
('Project79','G1000','2017.03',91918.11),
('Project108','G1000','2017.03',27279),
('Project110','G1000','2017.03',142071.75),
('Project16','G1000','2017.03',45931.49),
('Project17','G1000','2017.03',141555.9),
('Project63','G1000','2017.03',423716.64),
('Project72','G1000','2017.03',25000),
('Project18','G1006','2017.03',100000),
('Project130','G1000','2017.03',92710.75),
('Project106','G1006','2017.03',44209.5),
('Project36','G1000','2017.03',207394.05),
('Project93','G1006','2017.03',63300),
('Project129','G1000','2017.03',29137.75),
('Project20','G1006','2017.03',34584),
('Project38','G1000','2017.03',57324.15),
('Project22','G1000','2017.03',169847.4),
('Project28','G1000','2017.03',49000),
('Project131','G1000','2017.03',NULL),
('Project35','G1000','2017.03',81950),
('Project57','G1006','2017.03',700),
('Project95','G1000','2017.03',21500),
('Project29','G1005','2017.03',51000),
('Project84','G1006','2017.03',47994),
('Project128','G1000','2017.03',75000),
('Project103','G1000','2017.03',37500),
('Project89','G1006','2017.03',20500),
('Project88','G1006','2017.03',42444),
('Project99','G1006','2017.03',42500),
('Project47','G1000','2017.03',56086),
('Project69','G1006','2017.03',51107),
('Project70','G1000','2017.03',14400),
('Project82','G1006','2017.03',42382),
('Project102','G1006','2017.03',7823.55),
('Project56','G1006','2017.03',8749),
('Project78','G1006','2017.03',31867),
('Project76','G1006','2017.03',20000),
('Project5','G1000','2017.03',84764),
('Project105','G1000','2017.03',3630),
('Project7','G1000','2017.06',1886317.88),
('Project8','G1000','2017.06',133876.9),
('Project65','G1000','2017.06',36573.78),
('Project12','G1000','2017.06',20000),
('Project13','G1006','2017.06',154300),
('Project45','G1000','2017.06',275020),
('Project55','G1000','2017.06',31994.89),
('Project48','G1000','2017.06',50000),
('Project50','G1000','2017.06',67140.72),
('Project59','G1000','2017.06',21168),
('Project74','G1000','2017.06',54755),
('Project73','G1000','2017.06',3000),
('Project79','G1000','2017.06',7993.3),
('Project80','G1000','2017.06',250000),
('Project104','G1000','2017.06',125000),
('Project110','G1000','2017.06',94714.5),
('Project118','G1000','2017.06',50000),
('Project115','G1000','2017.06',25000),
('Project15','G1000','2017.06',46000),
('Project16','G1000','2017.06',57639.53),
('Project58','G1000','2017.06',100000),
('Project17','G1000','2017.06',116003),
('Project63','G1000','2017.06',239148.28),
('Project126','G1000','2017.06',25000),
('Project18','G1006','2017.06',71876.64),
('Project130','G1000','2017.06',92710.75),
('Project106','G1006','2017.06',19438),
('Project36','G1000','2017.06',207394.05),
('Project93','G1006','2017.06',50000),
('Project129','G1000','2017.06',29137.75),
('Project20','G1006','2017.06',19056),
('Project21','G1006','2017.06',50000),
('Project38','G1000','2017.06',43953.56),
('Project22','G1000','2017.06',158935.4),
('Project28','G1000','2017.06',49000),
('Project46','G1000','2017.06',44175.23),
('Project42','G1000','2017.06',905600),
('Project131','G1000','2017.06',181666.67),
('Project35','G1000','2017.06',81945.19),
('Project26','G1000','2017.06',10000),
('Project112','G1000','2017.06',20000),
('Project85','G1006','2017.06',40000),
('Project92','G1006','2017.06',67143),
('Project113','G1000','2017.06',90000),
('Project83','G1006','2017.06',5000),
('Project29','G1005','2017.06',45000),
('Project103','G1000','2017.06',37500),
('Project87','G1006','2017.06',20000),
('Project121','G1000','2017.06',75000),
('Project119','G1000','2017.06',5000),
('Project120','G1000','2017.06',3000),
('Project123','G1000','2017.06',75000),
('Project122','G1000','2017.06',15000),
('Project124','G1000','2017.06',15000),
('Project125','G1000','2017.06',3000),
('Project47','G1000','2017.06',15160),
('Project107','G1000','2017.06',27000),
('Project69','G1006','2017.06',22550),
('Project70','G1000','2017.06',30000),
('Project102','G1006','2017.06',86059.05),
('Project78','G1006','2017.06',98515),
('Project51','G1000','2017.09',60000),
('Project7','G1000','2017.09',1903395.51),
('Project8','G1000','2017.09',117317.6),
('Project65','G1000','2017.09',36573.78),
('Project9','G1000','2017.09',192479.65),
('Project10','G1000','2017.09',42268.65),
('Project11','G1000','2017.09',16745),
('Project34','G1000','2017.09',75000),
('Project48','G1000','2017.09',75000),
('Project73','G1000','2017.09',3000),
('Project80','G1000','2017.09',150000),
('Project104','G1000','2017.09',100000),
('Project110','G1000','2017.09',78928.75),
('Project118','G1000','2017.09',75000),
('Project114','G1000','2017.09',125000),
('Project115','G1000','2017.09',50000),
('Project117','G1000','2017.09',150000),
('Project116','G1000','2017.09',40000),
('Project15','G1000','2017.09',25000),
('Project58','G1000','2017.09',100000),
('Project17','G1000','2017.09',97372.66),
('Project63','G1000','2017.09',115052.53),
('Project72','G1000','2017.09',100000),
('Project126','G1000','2017.09',25000),
('Project130','G1000','2017.09',92710.75),
('Project36','G1000','2017.09',207394.05),
('Project93','G1006','2017.09',100780),
('Project129','G1000','2017.09',29137.75),
('Project20','G1006','2017.09',25000),
('Project38','G1000','2017.09',47953.56),
('Project22','G1000','2017.09',700641.24),
('Project66','G1000','2017.09',9631.35),
('Project131','G1000','2017.09',181666.67),
('Project112','G1000','2017.09',30000),
('Project85','G1006','2017.09',10000),
('Project92','G1006','2017.09',52143),
('Project113','G1000','2017.09',100000),
('Project29','G1005','2017.09',25000),
('Project84','G1006','2017.09',80604),
('Project103','G1000','2017.09',37500),
('Project87','G1006','2017.09',20000),
('Project121','G1000','2017.09',75000),
('Project119','G1000','2017.09',10000),
('Project123','G1000','2017.09',75000),
('Project122','G1000','2017.09',35000),
('Project124','G1000','2017.09',30000),
('Project125','G1000','2017.09',17000),
('Project47','G1000','2017.09',30520),
('Project107','G1000','2017.09',28000),
('Project69','G1006','2017.09',48389),
('Project82','G1006','2017.09',42382),
('Project102','G1006','2017.09',86059.05),
('Project78','G1006','2017.09',32839),
('Project5','G1000','2017.09',50858.4),
('Project7','G1000','2017.12',1843590.45),
('Project8','G1000','2017.12',36933),
('Project65','G1000','2017.12',18286.96),
('Project10','G1000','2017.12',792277.82),
('Project11','G1000','2017.12',15000),
('Project34','G1000','2017.12',75000),
('Project45','G1000','2017.12',308237.74),
('Project48','G1000','2017.12',75000),
('Project73','G1000','2017.12',3000),
('Project80','G1000','2017.12',150000),
('Project104','G1000','2017.12',100000),
('Project118','G1000','2017.12',150000),
('Project114','G1000','2017.12',125000),
('Project115','G1000','2017.12',75000),
('Project117','G1000','2017.12',150000),
('Project116','G1000','2017.12',80000),
('Project15','G1000','2017.12',31000),
('Project16','G1000','2017.12',9006.17),
('Project58','G1000','2017.12',100000),
('Project31','G1000','2017.12',41428.41),
('Project17','G1000','2017.12',54301.94),
('Project63','G1000','2017.12',55555.63),
('Project72','G1000','2017.12',200000),
('Project126','G1000','2017.12',25000),
('Project19','G1000','2017.12',561577.32),
('Project130','G1000','2017.12',92710.75),
('Project106','G1006','2017.12',4900),
('Project36','G1000','2017.12',224050.93),
('Project129','G1000','2017.12',29137.75),
('Project20','G1006','2017.12',25000),
('Project86','G1000','2017.12',2000),
('Project21','G1006','2017.12',50000),
('Project38','G1000','2017.12',63851.19),
('Project22','G1000','2017.12',125000),
('Project24','G1006','2017.12',50000),
('Project46','G1000','2017.12',44175.23),
('Project42','G1000','2017.12',59921),
('Project131','G1000','2017.12',181666.67),
('Project26','G1000','2017.12',10000),
('Project112','G1000','2017.12',50000),
('Project85','G1006','2017.12',45000),
('Project92','G1006','2017.12',52143),
('Project113','G1000','2017.12',100000),
('Project83','G1006','2017.12',5000),
('Project41','G1000','2017.12',500),
('Project29','G1005','2017.12',20000),
('Project84','G1006','2017.12',67916),
('Project103','G1000','2017.12',37500),
('Project87','G1006','2017.12',10000),
('Project121','G1000','2017.12',75000),
('Project119','G1000','2017.12',25000),
('Project123','G1000','2017.12',100000),
('Project122','G1000','2017.12',30000),
('Project124','G1000','2017.12',15000),
('Project125','G1000','2017.12',10000),
('Project47','G1000','2017.12',21400),
('Project102','G1006','2017.12',86059.05),
('Project78','G1006','2017.12',22000),
('Project5','G1000','2017.12',60394.35),
('Project51','G1000','2018.12',80000),
('Project7','G1000','2018.12',6899543.92),
('Project8','G1000','2018.12',81123.9),
('Project65','G1000','2018.12',105950.62),
('Project11','G1000','2018.12',31007),
('Project34','G1000','2018.12',150000),
('Project48','G1000','2018.12',100000),
('Project104','G1000','2018.12',100000),
('Project118','G1000','2018.12',525000),
('Project115','G1000','2018.12',150000),
('Project117','G1000','2018.12',250000),
('Project116','G1000','2018.12',80000),
('Project16','G1000','2018.12',60576.98),
('Project58','G1000','2018.12',120000),
('Project31','G1000','2018.12',141714.83),
('Project17','G1000','2018.12',551764.83),
('Project63','G1000','2018.12',586148.48),
('Project72','G1000','2018.12',360000),
('Project126','G1000','2018.12',50000),
('Project19','G1000','2018.12',326584.77),
('Project130','G1000','2018.12',370843),
('Project36','G1000','2018.12',846233.08),
('Project129','G1000','2018.12',116551),
('Project20','G1006','2018.12',70000),
('Project21','G1006','2018.12',400000),
('Project38','G1000','2018.12',120000),
('Project22','G1000','2018.12',930069.86),
('Project24','G1006','2018.12',287500),
('Project46','G1000','2018.12',13382.28),
('Project42','G1000','2018.12',533701),
('Project131','G1000','2018.12',545000),
('Project112','G1000','2018.12',200000),
('Project29','G1005','2018.12',150000),
('Project84','G1006','2018.12',214133),
('Project128','G1000','2018.12',75000),
('Project82','G1006','2018.12',84764),
('Project102','G1006','2018.12',388942.2),
('Project5','G1000','2018.12',144310.71),
('Project51','G1000','2019.12',60000),
('Project7','G1000','2019.12',4782397.34),
('Project31','G1000','2019.12',78014.19),
('Project17','G1000','2019.12',522097.43),
('Project63','G1000','2019.12',504668.12),
('Project72','G1000','2019.12',244500),
('Project19','G1000','2019.12',600528.16),
('Project36','G1000','2019.12',190405.42),
('Project20','G1006','2019.12',70000),
('Project21','G1006','2019.12',500000),
('Project38','G1000','2019.12',180000),
('Project22','G1000','2019.12',973906.04),
('Project24','G1006','2019.12',500000),
('Project42','G1000','2019.12',335626),
('Project112','G1000','2019.12',200000),
('Project29','G1005','2019.12',150000),
('Project128','G1000','2019.12',75000),
('Project7','G1000','2020.12',1449182.69),
('Project17','G1000','2020.12',522097.43),
('Project63','G1000','2020.12',473304.12),
('Project72','G1000','2020.12',244500),
('Project19','G1000','2020.12',352075.36),
('Project36','G1000','2020.12',387872.15),
('Project20','G1006','2020.12',20000),
('Project21','G1006','2020.12',500000),
('Project38','G1000','2020.12',180000),
('Project22','G1000','2020.12',350673.36),
('Project24','G1006','2020.12',600000),
('Project42','G1000','2020.12',35952),
('Project29','G1005','2020.12',150000),
('Project17','G1000','2021.12',522097.43),
('Project63','G1000','2021.12',457054.12),
('Project72','G1000','2021.12',244500),
('Project19','G1000','2021.12',648372.55),
('Project21','G1006','2021.12',500000),
('Project24','G1006','2021.12',600000),