Rollup summation in TSQL

Question

Hi Guys,

I need a help on below problem, where I have to write TSQL to be execute in loop ( or a batch because volume) of source data is high.

Below is sample, wherein if associate is moving from old department to new department then for all historical months,
previous net amount should get cancelled against old department and same amount (but positive) number needs to be added for next department.
So that books should show history and transition correctly.
/****** Object: Table [dbo].[SrcData] Script Date: 6/16/2022 12:50:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE [dbo].[SrcData]
go
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SrcData]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SrcData](
[DepartmentId] [bigint] NULL,
[old_DepartmentId] [bigint] NULL,
[EventFlag] [int] NOT NULL,

Salary [decimal](15, 6) NULL ,  
ROWNUMBER INT  

) ON [PRIMARY]
END
GO
INSERT [dbo].[SrcData] ([DepartmentId], [old_DepartmentId], [EventFlag], Salary,ROWNUMBER ) VALUES (7817, 7817, 0, CAST(1300.000000 AS Decimal(15, 6)) ,1)
GO
INSERT [dbo].[SrcData] ([DepartmentId], [old_DepartmentId], [EventFlag], Salary ,ROWNUMBER ) VALUES (7818, 7817, 1, CAST(962.040000 AS Decimal(15, 6)) ,2)
GO
INSERT [dbo].[SrcData] ([DepartmentId], [old_DepartmentId], [EventFlag], Salary ,ROWNUMBER) VALUES (7818, 7818, 0, CAST(-1300.000000 AS Decimal(15, 6)) ,3)
GO
INSERT [dbo].[SrcData] ([DepartmentId], [old_DepartmentId], [EventFlag], Salary,ROWNUMBER ) VALUES (7819, 7818, 1, CAST(1300.000000 AS Decimal(15, 6)) ,4)
GO
INSERT [dbo].[SrcData] ([DepartmentId], [old_DepartmentId], [EventFlag], Salary,ROWNUMBER ) VALUES (7819, 7819, 0, CAST(-962.040000 AS Decimal(15, 6)) ,5)
GO
INSERT [dbo].[SrcData] ([DepartmentId], [old_DepartmentId], [EventFlag], Salary ,ROWNUMBER ) VALUES (7820, 7819, 1, CAST(962.040000 AS Decimal(15, 6)) ,6)
GO
INSERT [dbo].[SrcData] ([DepartmentId], [old_DepartmentId], [EventFlag], Salary ,ROWNUMBER ) VALUES (7820, 7820, 0, CAST(-1300.000000 AS Decimal(15, 6)) ,7)
GO

SELECT * FROM [dbo].[SrcData]

I need to develop a generic TSQL to provide output as follow, please suggest and help with better option to implement this like recursive code etc..

Answer 1

Try a query:

;  
with T as  
(  
    select 'New' as [Process Description], Salary as Amount, DepartmentId as [New department], 0 as ad, ROWNUMBER, 0 as i  
    from SrcData  
    where old_DepartmentId = DepartmentId  
    union all  
    select 'Cancel', null, d2.old_DepartmentId, 0, d2.ROWNUMBER, 1  
    from SrcData d2  
    inner join SrcData d1 on d1.ROWNUMBER = d2.ROWNUMBER - 1  
    where d2.old_DepartmentId <> d2.DepartmentId  
    union all  
    select 'Reset', null, d2.DepartmentId, d2.old_DepartmentId, d2.ROWNUMBER, 2  
    from SrcData d2  
    inner join SrcData d1 on d1.ROWNUMBER = d2.ROWNUMBER - 1  
    where d2.old_DepartmentId <> d2.DepartmentId  
    union all  
    select 'New', d2.Salary, d2.DepartmentId, 0, d2.ROWNUMBER, 3  
    from SrcData d2  
    inner join SrcData d1 on d1.ROWNUMBER = d2.ROWNUMBER - 1  
    where d2.old_DepartmentId <> d2.DepartmentId  
)  
select   
    case [Process Description]   
        when 'Cancel' then concat('Cancel for ', [New department])  
        when 'Reset' then concat('Reset for above ', ad)  
        else [Process Description]   
    end as [Process Description],   
    format(  
        case [Process Description]   
            when 'New' then Amount   
            when 'Cancel' then -sum(Amount) over (order by ROWNUMBER, i)  
            when 'Reset' then sum(Amount) over (order by ROWNUMBER, i)  
        end, '0.##') as Amount,  
    [New department]  
from T  
order by ROWNUMBER, i  

Answer 2

TSQL to be execute in loop

We rarely use loops in SQL,
Screenshots are useless, because I can't query them
And at all, that's to less on information.
Please post table design as DDL, some sample data as DML statement and the expected result.