Select statement to calculate a running total
-
I'm using a select statement to fill a ASP.NET data grid. What I need to do is calculate a running total on a field as well as display all the other fields.
I've gotten it to display the overall total, but I want it to add the column as it goes. I'm not sure I can do this in a select statement or even a view?
Here' what I have
select left(a.DUEDATE,11) As 'Due Date', a.ORDERID as 'ORDERID',a.ORDSIZE as 'Order Size' ,sum(b.ORDSIZE) as 'Total' from vwgetservoo a, vwgetservoo b where a.part = b.part and a.part = 'abc' group by a.PART,a.orderid,a.ordsize, a.duedate
This produces
Due Date ORDERID Order Size Total
----------- ---------------------- ----------- ------
Sep 11 2002 .3024561.001.000.COD 2.0 5.0
Oct 25 2002 .3024561.003.000.COD 3.0 5.0
But I really want this: (NOTE the total field)
Due Date ORDERID Order Size Total
----------- ---------------------- ----------- ------
Sep 11 2002 .3024561.001.000.COD 2.0 2.0
Oct 25 2002 .3024561.003.000.COD 3.0 5.0
-
Just add this to the where clause. That should do the trick.
b.duedate <= a.dueDate
Two problems might arise.
1. The records are not sorted on duedate (is the total independent from orderid?)
2. you have multiple records for one part with the same duedate.
-
NPeeters,
I was going to say never mind I found the problem..and it was exactually what you suggested!! Thanks for the reply anyway!
Petey20
-
I like the 'solved it' post. Just to let other people in on your secret.
Just thinking about a solution for the problems I mentioned.
You could insert the complete records (in the correct order) in a temp table, adding an identity column. That way, you could do your comparison on the indentity column.
-
NPeeters,
That too was on my mind, I'm actually selecting from a view and the table the view is refering is part of my morning dumps from another database (Progress) and didn't want to have to modify the table if I had to. Just adding an identity field would hurt on my imports I don't believe, but since I got it to work this way...all is fine
Petey
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply