SQL FOR Calculate Closing Month & Year Wise

Question

I have Following table:

MONTHS	YearS	ItemGroupMasterId	OPUnitQuantity	TrnIwUnitQuantity	TrnOWUnitQuantity	closing
4	2021	30	27534.3	83792.1	102161.4	9165.04
5	2021	30	0	56806.49	52938.08	0
6	2021	30	0	43508.02	43367.98	0
7	2021	30	0	101539	97577.68	0
8	2021	30	0	109556.9	106798.7	0
9	2021	30	0	115160	110613.1	0
11	2021	30	0	101398.1	108433.9	0
12	2021	30	0	110334.2	112894.4	0
1	2022	30	0	98296.32	87840.05	0
2	2022	30	0	22868.14	44320.6	0
3	2022	30	0	75159.48	56382.23	0
4	2021	34	1194	1570	1444	1320
5	2021	34	0	648	561	0
6	2021	34	0	526	879	0

I want output Like Opening = Previous Closing group with ItemGroupMasterId & Calculate Closing Formula Like (OPUnitQuantity + TrnIwUnitQuantity) - TrnOWUnitQuantity.

I am using SQL Server 2008 r2.

MONTHS	YearS	ItemGroupMasterId	OPUnitQuantity	TrnIwUnitQuantity	TrnOWUnitQuantity	closing
4	2021	30	27534.3	83792.1	102161.4	9165.04
5	2021	30	9165.04	56806.49	52938.08	13033.45
6	2021	30	13033.45	43508.02	43367.98	13173.49
7	2021	30	13173.49	101539	97577.68	17134.81
8	2021	30	17134.81	109556.9	106798.7	19893.03
9	2021	30	19893.03	115160	110613.1	24439.85
11	2021	30	24439.85	101398.1	108433.9	17404.13
12	2021	30	17404.13	110334.2	112894.4	14843.97
1	2022	30	14843.97	98296.32	87840.05	25300.24
2	2022	30	25300.24	22868.14	44320.6	3847.78
3	2022	30	3847.78	75159.48	56382.23	22625.03
4	2021	34	1194	1570	1444	1320
5	2021	34	1320	648	561	1407
6	2021	34	1407	526	879	1054

Table Script

CREATE TABLE [dbo].[TestMaster..TestData](
    [MONTHS] [smallint] NULL,
    [YearS] [smallint] NULL,
    [ItemGroupMasterId] [smallint] NULL,
    [OPUnitQuantity] [numeric](18, 3) NULL,
    [TrnIwUnitQuantity] [numeric](18, 3) NULL,
    [TrnOWUnitQuantity] [numeric](18, 3) NULL,
    [closing] [numeric](18, 3) NULL
) ON [PRIMARY]

INSERT INTO  TestMaster..TestData 
(MONTHS,YearS,ItemGroupMasterId,OPUnitQuantity,TrnIwUnitQuantity,TrnOWUnitQuantity,closing)
Values (4,2021,30,27534.300,83792.100,102161.360,9165.040)
INSERT INTO  TestMaster..TestData 
(MONTHS,YearS,ItemGroupMasterId,OPUnitQuantity,TrnIwUnitQuantity,TrnOWUnitQuantity,closing)
Values (5,2021    ,30    ,0    ,56806.49    ,52938.08,    0)
INSERT INTO  TestMaster..TestData 
(MONTHS,YearS,ItemGroupMasterId,OPUnitQuantity,TrnIwUnitQuantity,TrnOWUnitQuantity,closing)
Values (6    ,2021,    30    ,0    ,43508.02    ,43367.98,0)
INSERT INTO  TestMaster..TestData
(MONTHS,YearS,ItemGroupMasterId,OPUnitQuantity,TrnIwUnitQuantity,TrnOWUnitQuantity,closing)
Values (7,    2021,    30,    0    ,101539,    97577.68,0)
INSERT INTO  TestMaster..TestData 
(MONTHS,YearS,ItemGroupMasterId,OPUnitQuantity,TrnIwUnitQuantity,TrnOWUnitQuantity,closing)
Values (8,    2021,    30    ,0    ,109556.92,    106798.7    ,0)
INSERT INTO  TestMaster..TestData
(MONTHS,YearS,ItemGroupMasterId,OPUnitQuantity,TrnIwUnitQuantity,TrnOWUnitQuantity,closing)
Values (9,    2021,    30,    0    ,115159.96    ,110613.14    ,0)
INSERT INTO  TestMaster..TestData
(MONTHS,YearS,ItemGroupMasterId,OPUnitQuantity,TrnIwUnitQuantity,TrnOWUnitQuantity,closing)
Values (11,    2021    ,30,    0    ,101398.135,    108433.86,0)
INSERT INTO  TestMaster..TestData
(MONTHS,YearS,ItemGroupMasterId,OPUnitQuantity,TrnIwUnitQuantity,TrnOWUnitQuantity,closing)
Values (12,    2021    ,30    ,0    ,110334.195    ,112894.35,0)
INSERT INTO  TestMaster..TestData
(MONTHS,YearS,ItemGroupMasterId,OPUnitQuantity,TrnIwUnitQuantity,TrnOWUnitQuantity,closing)
Values (1    ,2022    ,30    ,0    ,98296.32    ,87840.05,0)
INSERT INTO  TestMaster..TestData
(MONTHS,YearS,ItemGroupMasterId,OPUnitQuantity,TrnIwUnitQuantity,TrnOWUnitQuantity,closing)
Values (2,    2022    ,30,    0    ,22868.14    ,44320.6,0)
INSERT INTO  TestMaster..TestData
(MONTHS,YearS,ItemGroupMasterId,OPUnitQuantity,TrnIwUnitQuantity,TrnOWUnitQuantity,closing)
Values (3    ,2022    ,30,    0    ,75159.48    ,56382.23,0)
INSERT INTO  TestMaster..TestData
(MONTHS,YearS,ItemGroupMasterId,OPUnitQuantity,TrnIwUnitQuantity,TrnOWUnitQuantity,closing)
Values (4    ,2021    ,34    ,1194    ,1570    ,1444    ,1320)
INSERT INTO  TestMaster..TestData
(MONTHS,YearS,ItemGroupMasterId,OPUnitQuantity,TrnIwUnitQuantity,TrnOWUnitQuantity,closing)
Values (5    ,2021    ,34,    0    ,648,    561    ,0)
INSERT INTO  TestMaster..TestData
(MONTHS,YearS,ItemGroupMasterId,OPUnitQuantity,TrnIwUnitQuantity,TrnOWUnitQuantity,closing)
Values (6    ,2021    ,34,    0    ,526    ,879    ,0)

Amit Mohanty · Answer

Hey check the below query declare @finalTable table ( [MONTHS] [ smallint ] NULL , [YearS] [ smallint ] NULL , [ItemGroupMasterId] [ smallint ] NULL , [OPUnitQuantity] [ numeric ](18, 3) NULL , [TrnIwUnitQuantity] [ numeric ](18, 3) NULL , [TrnOWUnitQuantity] [ numeric ](18, 3) NULL , [closing] [ numeric ](18, 3) NULL ) declare @ table table ( sqlid int identity, masterid int ) insert into @ table select distinct itemgroupmasterid from TestMaster..TestData --select * from @table declare @tableCount int = 1 declare @tableCount1 int = ( select count (*) from @ table ) while @tableCount begin declare @masterid int = ( select masterid from @ table where sqlid = @tableCount) IF OBJECT_ID( 'tempdb..#tableMaster' ) IS NOT NULL DROP TABLE #tableMaster create table #tableMaster( sqlid int identity, [MONTHS] [ smallint ] NULL , [YearS] [ smallint ] NULL , [ItemGroupMasterId] [ smallint ] NULL , [OPUnitQuantity] [ numeric ](18, 3) NULL , [TrnIwUnitQuantity] [ numeric ](18, 3) NULL , [TrnOWUnitQuantity] [ numeric ](18, 3) NULL , [closing] [ numeric ](18, 3) NULL ); insert into #tableMaster select * from TestMaster..TestData where itemgroupmasterid = @masterid order by years, months --select * from @tableMaster declare @tableMasterCount int = 1 declare @tableMasterCount1 int = ( select count (*) from #tableMaster) declare @opQuantity numeric (18, 3) = 0 while @tableMasterCount begin if(@tableMasterCount = 1) set @opQuantity = ( select OPUnitQuantity from #tableMaster where sqlid = @tableMasterCount) declare @trnIwUnitQuantity numeric (18, 3) = ( select TrnIwUnitQuantity from #tableMaster where sqlid = @tableMasterCount) declare @trnOWUnitQuantity numeric (18, 3) = ( select TrnOWUnitQuantity from #tableMaster where sqlid = @tableMasterCount) declare @closingQuantity numeric (18, 3) = (@opQuantity + @trnIwUnitQuantity) - @trnOWUnitQuantity insert into @finalTable select months,years,itemgroupmasterid,@opQuantity,@trnIwUnitQuantity,@trnOWUnitQuantity,@closingQuantity from #tableMaster where sqlid = @tableMasterCount set @opQuantity = @closingQuantity set @tableMasterCount = @tableMasterCount + 1 end set @tableCount = @tableCount + 1 end select * from @finalTable