UnPivot, CrossApply

Farhan · July 10, 2020, 9:35am

Hi All,

The existing data I've in the Form-format show below; all the months showing in each column rather than rows.

ProjecIT , Project Name, Title , MP1, MP2 **.....**MP12, MO1, M02,....M12

So I've used the following UnPivot SQL ,
As above result the month for Plan are (MP1 to MP12) and for Actual are similar (M01 to M012) the issue

Step(1) I've split the existing data into Plan and Actual separate using following SQL
Step(2) To get the meaningful Month Period like 1,2,3...12 instead of (MP1, MP2...MP12) I've simply create the control Period table and join the relevant values to get the correct period
The issue , when I trying to join the PLAN and Actual its generated the error

Blockquoteselect
select
Plan_Tbl1.*
,IP_Period.Period -- Get the Period
from
(
select
Division_ID
,Project_ID
,Target
,FY21_Plan
,FY21_Value
,0 AS FY21_OutTurn
,0 aS FY21_OutTurn_Value
from CIP_Staging
unpivot
(
FY21_Value
for FY21_Plan in (FY21M1P, FY21M2P, FY21M3P, FY21M4P, FY21M5P, FY21M6P, FY21M7P, FY21M8P, FY21M9P, FY21M10P, FY21M11P, FY21M12P, FY22M1P, FY22M2P, FY22M3P, FY22M4P, FY22M5P, FY22M6P, FY22M7P, FY22M8P, FY22M9P, FY22M10P, FY22M11P, FY22M12P)
) unpiv
) as Plan_Tbl1

INNER JOIN IP_Period on Actual_tbl2.FY21_OutTurn = IP_period.Actual_Month

UNION ALL

SELECT
Actual_tbl2.*
,IP_Period.period
FROM
(
select
Division_ID
,Project_ID
,Target
,0 AS FY21_Plan
,0 aS FY21_Value
,FY21_OutTurn
,FY21_OutTurn_Value
from CIP_Staging
unpivot
(
FY21_OutTurn_Value
for FY21_OutTurn in (FY21M1O,	FY21M2O,	FY21M3O,	FY21M4O,	FY21M5O,	FY21M6O,	FY21M7O,	FY21M8O,	FY21M9O,	FY21M10O,	FY21M11O,	FY21M12O)
) unpiv
) as Actual_tbl2

INNER JOIN IP_Period on Actual_tbl2.FY21_OutTurn = IP_period.Actual_Month

Happy to share the sample DDL/DML scripts so you can test at your end make more sense to resolve the issue?

Thanks
Farhan

Farhan · July 10, 2020, 9:50am

drop table IP_Staging;
create table IP_Staging
(
 IPs_Division	Varchar(30)
,Division_ID	Varchar(20)
,Project_ID     Varchar(20)
,Title			Varchar(150)
,Description	Varchar(150)
,Cost_Centre	Integer
,Target				Integer
,FY21M1P			Integer
,FY21M2P			Integer
,FY21M3P	Integer
,FY21M4P	Integer
,FY21M5P	Integer
,FY21M6P	Integer
,FY21M7P	Integer
,FY21M8P	Integer
,FY21M9P	Integer
,FY21M10P	Integer
,FY21M11P	Integer
,FY21M12P	Integer
,FY22M1P	Integer
,FY22M2P	Integer
,FY22M3P	Integer
,FY22M4P	Integer
,FY22M5P	Integer
,FY22M6P	Integer
,FY22M7P	Integer
,FY22M8P	Integer
,FY22M9P	Integer
,FY22M10P	Integer
,FY22M11P	Integer
,FY22M12P	Integer
,FY21M1O	Integer
,FY21M2O	Integer
,FY21M3O	Integer
,FY21M4O	Integer
,FY21M5O	Integer
,FY21M6O	Integer
,FY21M7O	Integer
,FY21M8O	Integer
,FY21M9O	Integer
,FY21M10O	Integer
,FY21M11O	Integer
,FY21M12O	Integer
,Comments	Varchar(100)
);

--select * from IP_Staging

INSERT INTO IP_STAGING VALUES ('Surg','TRAN001','Res001',' Savings Plans','Test',499110,250000,20833,20833,20833,20833,20833,20833,20833,20833,20833,20833,20833,20837,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'Testing');
INSERT INTO IP_STAGING VALUES ('Surg','TRAN002','Con002','Medi Management','',498630,636000,53000,53000,53000,53000,53000,53000,53000,53000,53000,53000,53000,53000,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'');
INSERT INTO IP_STAGING VALUES ('Surg','TRAN003','Res003','Frailty and Integrated Care','',498570,1300000,108333,108333,108333,108333,108333,108333,108333,108333,108333,108333,108333,108337,0,0,0,0,0,0,0,0,0,0,0,0,20000,20000,50000,0,0,0,0,0,0,0,0,0,'');
INSERT INTO IP_STAGING VALUES ('Surg','TRAN004','Res004','Reserves Adjustment (to match Budget Upload)','',498570,122560.620000001,10213,10213,10213,10213,10213,10213,10213,10213,10213,10213,10213,10217.620000001,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'');
INSERT INTO IP_STAGING VALUES ('A & M','A&M001','Unknown','Reduction in Length of Stay','TBA how the performance is going to be measured and financial impact quantified (',null,100000,8333,8333,8333,8333,8333,8333,8333,8333,8333,8333,8333,8337,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'');

Farhan · July 10, 2020, 9:54am

How I created the Control table to get the correct Period (1,2,3.....12)

   drop table IP_Period;
create table IP_Period
(
  Year			INTEGER
 ,Plan_Month	VARCHAR(10)
 ,Actual_Month	VARCHAR(10)
 ,Period		INTEGER
 )

 INSERT INTO IP_Period VALUES(2021,'FY21M1P','FY21M1O',1);
INSERT INTO IP_Period VALUES(2021,'FY21M2P','FY21M2O',2);
INSERT INTO IP_Period VALUES(2021,'FY21M3P','FY21M3O',3);
INSERT INTO IP_Period VALUES(2021,'FY21M4P','FY21M4O',4);
INSERT INTO IP_Period VALUES(2021,'FY21M5P','FY21M5O',5);
INSERT INTO IP_Period VALUES(2021,'FY21M6P','FY21M6O',6);
INSERT INTO IP_Period VALUES(2021,'FY21M7P','FY21M7O',7);
INSERT INTO IP_Period VALUES(2021,'FY21M8P','FY21M8O',8);
INSERT INTO IP_Period VALUES(2021,'FY21M9P','FY21M9O',9);
INSERT INTO IP_Period VALUES(2021,'FY21M10P','FY21M10O',10);
INSERT INTO IP_Period VALUES(2021,'FY21M11P','FY21M11O',11);
INSERT INTO IP_Period VALUES(2021,'FY21M12P','FY21M12O',12);
INSERT INTO IP_Period VALUES(2022,'FY22M1P','',1);
INSERT INTO IP_Period VALUES(2022,'FY22M2P','',2);
INSERT INTO IP_Period VALUES(2022,'FY22M3P','',3);
INSERT INTO IP_Period VALUES(2022,'FY22M4P','',4);
INSERT INTO IP_Period VALUES(2022,'FY22M5P','',5);
INSERT INTO IP_Period VALUES(2022,'FY22M6P','',6);
INSERT INTO IP_Period VALUES(2022,'FY22M7P','',7);
INSERT INTO IP_Period VALUES(2022,'FY22M8P','',8);
INSERT INTO IP_Period VALUES(2022,'FY22M9P','',9);
INSERT INTO IP_Period VALUES(2022,'FY22M10P','',10);
INSERT INTO IP_Period VALUES(2022,'FY22M11P','',11);
INSERT INTO IP_Period VALUES(2022,'FY22M12P','',12);

mike01 · July 10, 2020, 6:05pm

Looks like ur joins are all wrong

drop table #IP_Period;
create table #IP_Period
(
Year INTEGER
,Plan_Month VARCHAR(10)
,Actual_Month VARCHAR(10)
,Period INTEGER
)

INSERT INTO #IP_Period VALUES(2021,'FY21M1P','FY21M1O',1);
INSERT INTO #IP_Period VALUES(2021,'FY21M2P','FY21M2O',2);
INSERT INTO #IP_Period VALUES(2021,'FY21M3P','FY21M3O',3);
INSERT INTO #IP_Period VALUES(2021,'FY21M4P','FY21M4O',4);
INSERT INTO #IP_Period VALUES(2021,'FY21M5P','FY21M5O',5);
INSERT INTO #IP_Period VALUES(2021,'FY21M6P','FY21M6O',6);
INSERT INTO #IP_Period VALUES(2021,'FY21M7P','FY21M7O',7);
INSERT INTO #IP_Period VALUES(2021,'FY21M8P','FY21M8O',8);
INSERT INTO #IP_Period VALUES(2021,'FY21M9P','FY21M9O',9);
INSERT INTO #IP_Period VALUES(2021,'FY21M10P','FY21M10O',10);
INSERT INTO #IP_Period VALUES(2021,'FY21M11P','FY21M11O',11);
INSERT INTO #IP_Period VALUES(2021,'FY21M12P','FY21M12O',12);
INSERT INTO #IP_Period VALUES(2022,'FY22M1P','',1);
INSERT INTO #IP_Period VALUES(2022,'FY22M2P','',2);
INSERT INTO #IP_Period VALUES(2022,'FY22M3P','',3);
INSERT INTO #IP_Period VALUES(2022,'FY22M4P','',4);
INSERT INTO #IP_Period VALUES(2022,'FY22M5P','',5);
INSERT INTO #IP_Period VALUES(2022,'FY22M6P','',6);
INSERT INTO #IP_Period VALUES(2022,'FY22M7P','',7);
INSERT INTO #IP_Period VALUES(2022,'FY22M8P','',8);
INSERT INTO #IP_Period VALUES(2022,'FY22M9P','',9);
INSERT INTO #IP_Period VALUES(2022,'FY22M10P','',10);
INSERT INTO #IP_Period VALUES(2022,'FY22M11P','',11);
INSERT INTO #IP_Period VALUES(2022,'FY22M12P','',12);

drop table IP_Staging;
create table #IP_Staging
(
IPs_Division Varchar(30)
,Division_ID Varchar(20)
,Project_ID Varchar(20)
,Title Varchar(150)
,Description Varchar(150)
,Cost_Centre Integer
,Target Integer
,FY21M1P Integer
,FY21M2P Integer
,FY21M3P Integer
,FY21M4P Integer
,FY21M5P Integer
,FY21M6P Integer
,FY21M7P Integer
,FY21M8P Integer
,FY21M9P Integer
,FY21M10P Integer
,FY21M11P Integer
,FY21M12P Integer
,FY22M1P Integer
,FY22M2P Integer
,FY22M3P Integer
,FY22M4P Integer
,FY22M5P Integer
,FY22M6P Integer
,FY22M7P Integer
,FY22M8P Integer
,FY22M9P Integer
,FY22M10P Integer
,FY22M11P Integer
,FY22M12P Integer
,FY21M1O Integer
,FY21M2O Integer
,FY21M3O Integer
,FY21M4O Integer
,FY21M5O Integer
,FY21M6O Integer
,FY21M7O Integer
,FY21M8O Integer
,FY21M9O Integer
,FY21M10O Integer
,FY21M11O Integer
,FY21M12O Integer
,Comments Varchar(100)
);

INSERT INTO #IP_STAGING VALUES ('Surg','TRAN001','Res001',' Savings Plans','Test',499110,250000,20833,20833,20833,20833,20833,20833,20833,20833,20833,20833,20833,20837,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'Testing');
INSERT INTO #IP_STAGING VALUES ('Surg','TRAN002','Con002','Medi Management','',498630,636000,53000,53000,53000,53000,53000,53000,53000,53000,53000,53000,53000,53000,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'');
INSERT INTO #IP_STAGING VALUES ('Surg','TRAN003','Res003','Frailty and Integrated Care','',498570,1300000,108333,108333,108333,108333,108333,108333,108333,108333,108333,108333,108333,108337,0,0,0,0,0,0,0,0,0,0,0,0,20000,20000,50000,0,0,0,0,0,0,0,0,0,'');
INSERT INTO #IP_STAGING VALUES ('Surg','TRAN004','Res004','Reserves Adjustment (to match Budget Upload)','',498570,122560.620000001,10213,10213,10213,10213,10213,10213,10213,10213,10213,10213,10213,10217.620000001,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'');
INSERT INTO #IP_STAGING VALUES ('A & M','A&M001','Unknown','Reduction in Length of Stay','TBA how the performance is going to be measured and financial impact quantified (',null,100000,8333,8333,8333,8333,8333,8333,8333,8333,8333,8333,8333,8337,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'')

select
Plan_Tbl1.*
,[Period] -- Get the Period
from
(
select
Division_ID
,Project_ID
,Target
,FY21_Plan
,FY21_Value
,'0' AS FY21_OutTurn
,0 aS FY21_OutTurn_Value
from #IP_Staging
unpivot
(
FY21_Value
for FY21_Plan in (FY21M1P, FY21M2P, FY21M3P, FY21M4P, FY21M5P, FY21M6P, FY21M7P, FY21M8P, FY21M9P, FY21M10P, FY21M11P, FY21M12P, FY22M1P, FY22M2P, FY22M3P, FY22M4P, FY22M5P, FY22M6P, FY22M7P, FY22M8P, FY22M9P, FY22M10P, FY22M11P, FY22M12P)
) unpiv
) as Plan_Tbl1

INNER JOIN #IP_Period p on Plan_Tbl1.FY21_Plan= p.Plan_Month

UNION ALL

SELECT
Actual_tbl2.*
,[period]
FROM
(
select
Division_ID
,Project_ID
,Target
,'0' AS FY21_Plan
,0 aS FY21_Value
,FY21_OutTurn
,FY21_OutTurn_Value

from #IP_Staging
unpivot
(
FY21_OutTurn_Value
for FY21_OutTurn in (FY21M1O, FY21M2O, FY21M3O, FY21M4O, FY21M5O, FY21M6O, FY21M7O, FY21M8O, FY21M9O, FY21M10O, FY21M11O, FY21M12O)
) unpiv
) as Actual_tbl2

INNER JOIN #IP_Period on Actual_tbl2.FY21_OutTurn = Actual_Month

Farhan · July 14, 2020, 2:29pm

Any suggestions pleasse

yosiasz · July 14, 2020, 3:14pm

Did you try @mike01 suggestion

harishgg1 · July 14, 2020, 3:30pm

hi

could you please post .. diagram .. excel sheet
of your data and expected output ..

i saw your data in the first post ..