I’m still pretty new to SQL and I’m trying to perform a calculation that references the output from a calculation that was performed on the same table in a previous row/time period.
[Sample of table]
| Full Part | Quarter Yr | Name | Value | |-----------|------------|---------------|-----------| | 470205-17 | 42019 | Q1 FY 20 CCBP | 12524 | | 470205-17 | 42019 | Q1 FY 20 SP | 579.0005 | | 470205-17 | 42019 | Q1 FY 21 CCBP | 17436 | | 470205-17 | 42019 | Q1 FY 21 SP | 885.9997 | | 470205-17 | 42019 | Q2 FY 20 CCBP | 14807 | | 470205-17 | 42019 | Q2 FY 20 SP | 764.9999 | | 470205-17 | 42019 | Q2 FY 21 CCBP | 15220 | | 470205-17 | 42019 | Q2 FY 21 SP | 851.9996 | | 470205-17 | 42019 | Q3 FY 20 CCBP | 16196 | | 470205-17 | 42019 | Q3 FY 20 SP | 1066.9998 | | 470205-17 | 42019 | Q4 FY 19 CCBP | 4841 | | 470205-17 | 42019 | Q4 FY 19 SP | 730.0005 | | 470205-17 | 42019 | Q4 FY 20 CCBP | 17100 | | 470205-17 | 42019 | Q4 FY 20 SP | 1115.9995 | | 470205-17 | 42019 | Total QOH | 2181 |
CREATE TABLE mytable(
Full_Part VARCHAR(9) NOT NULL
,Quarter_Yr INTEGER NOT NULL
,Name VARCHAR(13) NOT NULL
,Value NUMERIC(9,4) NOT NULL
);
INSERT INTO mytable(Full_Part,Quarter_Yr,Name,Value) VALUES
('470205-17',42019,'Q1 FY 20 CCBP',12524)
,('470205-17',42019,'Q1 FY 20 SP',579.0005)
,('470205-17',42019,'Q1 FY 21 CCBP',17436)
,('470205-17',42019,'Q1 FY 21 SP',885.9997)
,('470205-17',42019,'Q2 FY 20 CCBP',14807)
,('470205-17',42019,'Q2 FY 20 SP',764.9999)
,('470205-17',42019,'Q2 FY 21 CCBP',15220)
,('470205-17',42019,'Q2 FY 21 SP',851.9996)
,('470205-17',42019,'Q3 FY 20 CCBP',16196)
,('470205-17',42019,'Q3 FY 20 SP',1066.9998)
,('470205-17',42019,'Q4 FY 19 CCBP',4841)
,('470205-17',42019,'Q4 FY 19 SP',730.0005)
,('470205-17',42019,'Q4 FY 20 CCBP',17100)
,('470205-17',42019,'Q4 FY 20 SP',1115.9995)
,('470205-17',42019,'Total QOH',2181);
SP is forecast , QOH is Current inventory and CCBP is Supply
What I need as output for Quarter YR=42019 is (Total QOH+Q4 FY 19 CCBP)-Q4 FY 19 SP
Then for quarter Yr=12020, I need Ending Inventory(Output of previous calculation)+(Q1 FY 20 CCBP)-Q1 FY 20 SP
And so on until QuarterYr=22021
The output needs to be in the same format i.e Full Part, QuartrYr,Name=’Ending Inventory’, Value
| Full Part | Quarter Yr | Name | Value | |-----------|------------|------------------|-------| | 470205-17 | 42019 | Ending Inventory | 6292 | | 470205-17 | 12020 | Ending Inventory | 18237 | | 470205-17 | 22020 | Ending Inventory | 32280 | | 470205-17 | 32020 | Ending Inventory | 47409 | | 470205-17 | 42020 | Ending Inventory | 63393 | | 470205-17 | 12021 | Ending Inventory | 79944 | | 470205-17 | 22021 | Ending Inventory | 94313 |
I’m assuming that this needs recursion/for loops to achieve the desired output. Any help in making this work would be greatly appreciated.
Advertisement
Answer
Tested on SQL Server 2017:
Here’s an Example query to produce the desired results:
declare @mytable table(
Full_Part VARCHAR(9) NOT NULL
,Quarter_Yr INTEGER NOT NULL
,Name VARCHAR(13) NOT NULL
,Value NUMERIC(9,4) NOT NULL
);
INSERT INTO @mytable(Full_Part,Quarter_Yr,Name,Value) VALUES
('470205-17',42019,'Q1 FY 20 CCBP',12524)
,('470205-17',42019,'Q1 FY 20 SP',579.0005)
,('470205-17',42019,'Q1 FY 21 CCBP',17436)
,('470205-17',42019,'Q1 FY 21 SP',885.9997)
,('470205-17',42019,'Q2 FY 20 CCBP',14807)
,('470205-17',42019,'Q2 FY 20 SP',764.9999)
,('470205-17',42019,'Q2 FY 21 CCBP',15220)
,('470205-17',42019,'Q2 FY 21 SP',851.9996)
,('470205-17',42019,'Q3 FY 20 CCBP',16196)
,('470205-17',42019,'Q3 FY 20 SP',1066.9998)
,('470205-17',42019,'Q4 FY 19 CCBP',4841)
,('470205-17',42019,'Q4 FY 19 SP',730.0005)
,('470205-17',42019,'Q4 FY 20 CCBP',17100)
,('470205-17',42019,'Q4 FY 20 SP',1115.9995)
,('470205-17',42019,'Total QOH',2181);
WITH PARSE1 AS (
SELECT
CASE WHEN Name LIKE 'Q_ FY%' THEN SUBSTRING(Name,2,1) ELSE NULL END AS N1,
CASE WHEN Name LIKE 'Q_ FY%' THEN SUBSTRING(Name,7,2) ELSE NULL END AS N2,
CASE WHEN Name LIKE 'Q_ FY%' THEN SUBSTRING(Name,10,4)
WHEN NAME = 'Total QOH' THEN 'QOH' ELSE NULL END AS N3,
Full_Part, Quarter_Yr, Value
FROM @mytable
), PARSE2 AS (
SELECT Full_Part, Quarter_Yr, Value, N3, CASE WHEN N3 = 'SP' THEN -1 ELSE 1 END AS mValue, CAST(N1 AS INT) AS Q, 2000 + CAST(N2 AS INT) AS YR
FROM PARSE1
), PIVOT1 AS (
SELECT Q, YR, Full_Part, Quarter_Yr, SUM(Value * mValue) AS Qtr_Value
FROM PARSE2
GROUP BY Q, YR, Full_Part, Quarter_Yr
), ANALYTIC1 AS (
SELECT Q, YR, Full_Part, Quarter_Yr, Qtr_Value,
SUM(Qtr_Value) OVER (PARTITION BY [Full_Part] ORDER BY YR, Q) AS Run_Value
FROM PIVOT1
)
SELECT Full_Part AS [Full Part], CONCAT(Q,YR) AS [Quarter Yr], 'Ending Inventory' AS Name, CAST(ROUND(Run_Value,1) AS INT) AS Value
FROM ANALYTIC1
WHERE Q IS NOT NULL
Output:
Full Part Quarter Yr Name Value 470205-17 42019 Ending Inventory 6292 470205-17 12020 Ending Inventory 18237 470205-17 22020 Ending Inventory 32279 470205-17 32020 Ending Inventory 47408 470205-17 42020 Ending Inventory 63392 470205-17 12021 Ending Inventory 79942 470205-17 22021 Ending Inventory 94310
About the different Common Table Expressions – CTE(s)
PARSE1/PARSE2:
- Some key data is hidden inside of the string “Name”.
- We need to extract out the Data Type, Quarter, and Year information (Assuming 2000 + Two Digits available).
- We’ll also plan to subtract SP by multiplying the Value by negative 1.
PIVOT1:
- Now we can apply our group by function to sum up the quarterly parts.
ANALYTIC1:
- Using a special sum analytic function, we can create a running tally when ordering by Quarter_Yr.
- An partition by clause is included to create a running tally within each logical set of data.
FINAL SELECT:
- A final clean up query is provided to present the data as desired.
You can replace ROUND() with CEILING() or FLOOR() to fine tune the results.
To inspect each individual CTE, You can replace the final select with:
- SELECT * FROM PARSE1
- SELECT * FROM PARSE2
- SELECT * FROM PIVOT1
- SELECT * FROM ANALYTIC1