Post
Ask Question
jaymin sathavara

jaymin sathavara

  • NA
  • 31
  • 2.2k

SQL FOR Calculate Closing Month & Year Wise

Jun 27 2022 6:15 AM

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)

 


Answers (1)