Get Running Total of each year previous year's closing value from different column-CodePudding

I am trying to sum each year's quantity but I also want to add previous year's closing quantity in this sum.

Following is the sample dataset.

DROP TABLE IF EXISTS #tmpTable
CREATE TABLE #tmpTable (
    FinancialYear varchar(max),
    Date date,
    ItemName varchar(255),
    InQTY int,
    ClosingQTY int)

INSERT INTO #tmpTable (FinancialYear, Date, ItemName, InQTY, ClosingQTY) VALUES
('2021-22', CONVERT(DATETIME, '2021-04-05', 120), 'ItemA', 5, 5),
('2021-22', CONVERT(DATETIME, '2021-05-17', 120), 'ItemA', 3, 7),
('2021-22', CONVERT(DATETIME, '2021-11-09', 120), 'ItemA', 2, 9),
('2021-22', CONVERT(DATETIME, '2022-02-25', 120), 'ItemA', NULL, 7),
('2022-23', CONVERT(DATETIME, '2022-04-02', 120), 'ItemA', 2, 9),
('2022-23', CONVERT(DATETIME, '2022-11-01', 120), 'ItemA', 3, 11),
('2022-23', CONVERT(DATETIME, '2022-12-14', 120), 'ItemA', 4, 15)
GO

SELECT * FROM #tmpTable

Sample Table:

╔═══════════════╤════════════╤══════════╤═══════╤════════════╗
║ FinancialYear │ Date       │ ItemName │ InQTY │ ClosingQTY ║
╠═══════════════╪════════════╪══════════╪═══════╪════════════╣
║ 2021-22       │ 2021-04-05 │ ItemA    │ 5     │ 5          ║
╟───────────────┼────────────┼──────────┼───────┼────────────╢
║ 2021-22       │ 2021-05-17 │ ItemA    │ 3     │ 7          ║
╟───────────────┼────────────┼──────────┼───────┼────────────╢
║ 2021-22       │ 2021-11-09 │ ItemA    │ 2     │ 9          ║
╟───────────────┼────────────┼──────────┼───────┼────────────╢
║ 2021-22       │ 2022-02-25 │ ItemA    │ NULL  │ 7          ║
╟───────────────┼────────────┼──────────┼───────┼────────────╢
║ 2022-23       │ 2022-04-02 │ ItemA    │ 2     │ 9          ║
╟───────────────┼────────────┼──────────┼───────┼────────────╢
║ 2022-23       │ 2022-11-01 │ ItemA    │ 3     │ 11         ║
╟───────────────┼────────────┼──────────┼───────┼────────────╢
║ 2022-23       │ 2022-12-14 │ ItemA    │ 4     │ 15         ║
╚═══════════════╧════════════╧══════════╧═══════╧════════════╝

I tried "Over (Partition By )" to get each year's running sum.

SELECT a.FinancialYear, a.Date, a.ItemName, a.InQTY, a.ClosingQTY 
,SUM(a.InQTY)  OVER (PARTITION BY a.ItemName, a.FinancialYear  ORDER BY a.Date) [RunningInQTY]
FROM #tmpTable a

But I am not able to find a way to add previous year's closing QTY which is lying in another column and last row before beginning of new year.

Following is the expected result (marked in red box)

CodePudding user response：

Here you go!!

SELECT DATE, INQTY, CLOSINGQTY, 
FINANCIALYEAR,SUM(INQTY_NEW)  OVER (PARTITION BY FinancialYear  ORDER BY Date) AS ROLL_SUM
FROM (
select  DATE, INQTY, CLOSINGQTY, 
FINANCIALYEAR,
CASE WHEN FINANCIALYEAR<>(LAG(FINANCIALYEAR) OVER (ORDER BY FINANCIALYEAR)) THEN NVL(INQTY,0) NVL(LAG(CLOSINGQTY) OVER (ORDER BY FINANCIALYEAR),0) ELSE INQTY END AS INQTY_NEW
FROM TABLE);

Result:

Improve "Financial year equality" by using Regex and check year=year-1 if you need.

CodePudding user response：

Try using lag() function. It will bring the value in the previous row together so that you can add them together.

lag(ClosingQTY) over (partition by 'col' order by FinancialYear) as previous_year_Closing_Qty.