ASP Classic SQL query to get cumulative ammounts per day

Question

I am struggling with a SQL query I have a collection process and I want to have a query that show me every day how much we have collected so far during the current month in a cumulative way. I have all the information in one table named Procesado the date field is Process Date and the amount is in lcamnt Field.

So far I have this:

Query:

SELECT 
    Procesado.ProcessDate, SUM(Procesado.lcamnt) AS Monto 
FROM 
    Procesado 
WHERE 
    Procesado.ProcessDate >= Procesado.ProcessDate 
GROUP BY 
    Procesado.ProcessDate

Table value

Table Name: Procesado

ProcessDate  lcamnt  
05/26/2016   $1000  
05/26/2016   $500  
05/27/2016   $2000  
05/27/2016   $1000  
05/28/2016   $5000

Desired output

05/26/2016   $1500  
05/27/2016   $4500  
05/28/2016   $9500

If ProcessDate is a datetime field, you should just use convert in your group by to remove all the times. — Chuck, Jun 01 '16 at 16:22
I would try remove the "Where Procesado.ProcessDate>=Procesado.ProcessDate", I would think that you would get nothing from that. — Chuck, Jun 01 '16 at 16:25
the problem is that i am using amcharts to charting the output but so far the actual query just show me the collected per day i need to see per day the cumulative result so far in order to take decisions in case we are not meeting the collection target — Jonathan JC, Jun 01 '16 at 16:37
...though on second thought, the linked question is for when you have a unique ID column. So in this case you'd have to first do a plain GROUP BY query, and then get a cumulative sum of that, at which point you might as well just do the running total in VBScript. — Martha, Jun 01 '16 at 18:17
See the following SO article for details: http://stackoverflow.com/q/113045/964043 But you should be able to do GROUP BY CONVERT(date, Procesado.ProcessDate) as long as you are SQL 2008 or newer. — dmarietta, Jun 01 '16 at 18:23

score 0 · Accepted Answer · answered Jun 01 '16 at 19:15

0

select p.ProcessDate,
       (select sum(lcamnt)
        from procesado p2
        where 
              p2.ProcessDate <= p.ProcessDate
       ) as Monto
from  procesado p
group by p.processDate

answered Jun 01 '16 at 19:15

Tudor Saru

187
2
7

if this answer helped you please mark it as the correct answer.. thanks – Tudor Saru Jun 01 '16 at 19:16
2

Why didn't you just edit the other answer?, we have now lost all that useful information from the comments because you've deleted it! – user692942 Jun 01 '16 at 19:18
It won't let me for some reason – Tudor Saru Jun 01 '16 at 19:19

ASP Classic SQL query to get cumulative ammounts per day

1 Answers1