15,945,119 members
1.00/5 (1 vote)
See more:
i want to calculate Cumulative value

Date Prod_Type Chainage Material_Id QTY_FTD Last_Updated
2013-02-13 1 192 2 120.00 2013-02-13 13:27:21.660
2013-02-13 1 247 2 560.00 2013-02-13 13:27:21.500
2013-02-13 1 192 2 400.00 2013-02-13 13:27:21.400
2013-02-12 1 192 2 150.00 2013-02-13 13:27:21.320
2013-02-14 1 192 2 200.00 2013-02-13 13:27:21.100
from the above table 192,247 are Chainage's , 2's are material_Id's, 1's are Production_Type's , 120.00 , 400.00 's are QTY_FTD 's

i want to calculate the QTY_Cumilative ie,i will pass Material_Id , Production_Type and i want to calculate the SUM(QTY_FTD) from the Last Last_Updated date for a particular Chainage

output required:
Prod_Type Chainage Material_Id QTY_Cumilative
1 192 2 1270
1 247 2 560
Posted
Sergey Alexandrovich Kryukov 18-Feb-13 0:26am
What did you try so far?
—SA
sk. maqdoom ali 18-Feb-13 0:34am
i written the following query

(select sum(Qty_FTD) from Production_Details pr where Material_Id=@MaterialType AND Production_Type=@ProductionType and pr.Last_Updated <= pr.Last_Updated )
i am getting output as
Prod_Type Chainage Material_Id QTY_Cumilative
1 192 2 1830
1 247 2 1830
Zoltán Zörgő 18-Feb-13 0:36am
i suppose you are missing `group by Prod_Type, Chainage, Material_Id`

## Solution 2

Hi SK,

Please find the below sample example for Cumulative Calculation.

SQL
```Create table #tt1 (sno int identity,Amt int)

insert into #tt1 values (100)
insert into #tt1 values (200)
insert into #tt1 values (300)
insert into #tt1 values (400)

select * from #tt1

select a.sno,a.Amt,Sum(b.Amt) as Cum_Amt from #tt1 a cross join #tt1 b
where a.sno>=b.sno group by a.sno,a.Amt
order by a.sno,a.Amt```

## Solution 1

SQL
```create table #cumm_avg
([Date] datetime,Prod_Type int,Chainage Int,Material_Id int,Qty_Ftd float,Last_Updated datetime)
insert into #cumm_avg
values
('2013-02-13', 1, 192, 2, 120.00,'2013-02-13 13:27:21.660'),
('2013-02-13', 1, 247, 2, 560.00 ,'2013-02-13 13:27:21.500'),
('2013-02-13', 1, 192, 2, 400.00, '2013-02-13 13:27:21.400'),
('2013-02-12', 1, 192, 2, 150.00, '2013-02-13 13:27:21.320'),
('2013-02-14', 1, 192, 2, 200.00, '2013-02-13 13:27:21.100')

select Prod_Type,Chainage,Material_Id,SUM(Qty_Ftd) as Qty_Cummulative
from #cumm_avg
group by Prod_Type,Chainage,Material_Id```