Click here to Skip to main content
15,917,645 members
Please Sign up or sign in to vote.
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
Comments
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

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
 
Share this answer
 
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900