Rollup summation in TSQL

Sandip Satale 1 Reputation point
2022-06-16T06:30:33.753+00:00

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]

211866-problemexample.png

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..
211850-expectedoutput.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,597 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 83,256 Reputation points
    2022-06-16T18:11:21.863+00:00

    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  
    
    No comments

  2. Olaf Helper 26,056 Reputation points
    2022-06-16T06:39:23.71+00:00

    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.