how to calculate Cumulative value

Question

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 17-Feb-13 18:18pm

sk. maqdoom ali

Add a Solution

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

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Arun1_m1 · Answer 1 · 2013-02-17T19:23:00

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

Arul R Ece · Answer 2 · 2013-02-19T20:27:00

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

how to calculate Cumulative value

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0