SQL FOR Calculate Closing Month & Year Wise

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)

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 <= @tableCount1
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 <= @tableMasterCount1
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

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty4y ago
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 <= @tableCount1
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 <= @tableMasterCount1
    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
0