How to create running totals with this query?

Here is a good article to do what you are looking for:

http://www.mssqltips.com/tip.asp?tip=1686

You should not be posting duplicate questions:

I posted the answer in your original, somewhat related, question.

https://www.experts-exchange.com/questions/24110414/Group-by-date-with-hours-in-sql-query.html?anchorAnswerId=23624685#a23624685

On SQL 2000, you have to use a temp table.

create table #t (i int identity,theDay datetime, cnt int)
 
insert into #t (theDay,cnt)
select
  dateadd(dd, datediff(dd, 0, datestamp)+0, 0) 
, SUM(target_qty) as sumTargetQty
 
from TB_Actual
group by dateadd(dd, datediff(dd, 0, datestamp)+0, 0) 
order by dateadd(dd, datediff(dd, 0, datestamp)+0, 0) 
 
select theDay,cnt, (select sum(cnt) from #t where i <= t.i) Runningtotal
from #t t
 
drop table #t

Select allOpen in new window

sorry, i prefer to use queries instead of temp tables,

Can you be more clear with your sample data. I have observed that you added 2/1/2009 2/2/2009 and 2/3/2009 to your expected result but the qty values are not adjusted.

Observe the highlighted record for calrificatin.
 
02/04/2009 4 6527 6286
02/05/2009 4 6989 6557
02/06/2009 4 5385 6557
02/07/2009 4 4799 6557
02/08/2009 4 0 6557
02/09/2009 4 3766 6557
02/10/2009 4 4295 6557
02/11/2009 4 4052 6196

datestamp actual_qty_total target_qty_total running_actual running_target
2/1/2009 6527 6286 6527 6286
2/2/2009 6989 6557 13516 12843
2/3/2009 5385 6557 18901 19400
2/4/2009 4799 6557 23700 25957
2/5/2009 0 6557 23700 32514
2/6/2009 3766 6557 27466 39071
2/7/2009 4295 6557 31761 45628
2/8/2009 4052 6196 35813 51824

i apologize for making a mistake adding all this up. it becomes difficult to add big numbers. Basically the two extra columns to be the running totals for the two other columns.

To illustrate my point, I will illustrate the dates you questioned, but it should start from 2/1 -2/28

02/04/2009 4 6527 6286   6527   6286
02/05/2009 4 6989 6557  13516   12843

basically adding the items in the same column and creating a running total.

So what would be the qty values for 2/1/2009 2/2/2009 and 2/3/2009 ? All zeros? ( As no qty values are there for these dates)

if there are no data, it will look like this:

02/01/2009 4   0       0            0        0
02/02/2009 4   0       0            0        0
02/03/2009 4   0       0            0        0
02/04/2009 4 6527 6286   6527   6286
02/05/2009 4 6989 6557  13516   12843

but basically, on 2/4 its adding up all the quantities from 2/1-2/4
and on 2/5 its adding up all the quantities from 2/1-2/5
and so forth. does this make sense?? like a running total?

Please try this:


Select Convert( varchar(20),datestamp,101) as datestamp, board_id,
    Sum(actual_qty), Sum(target_qty),
    (select sum(ta2.actual_qty) from tb_actual ta2
     where ta2.board_id = ta.board_id
     and datestamp >'02/04/2009'
     and datestamp <=getdate()
     and ta2.datestamp <= Convert( varchar(20),ta.datestamp,101)) AS running_actual,
    (select sum(ta2.target_qty) from tb_actual ta2
     where ta2.board_id = ta.board_id
     and datestamp >'02/04/2009'
     and datestamp <=getdate()
     and ta2.datestamp <= Convert( varchar(20),ta.datestamp,101)) AS running_target
from TB_Actual ta
WHERE datestamp >'02/04/2009'
and datestamp <=getdate()
and board_id='4'
Group by Convert( varchar(20),datestamp,101) , board_id
order by Convert( varchar(20),datestamp,101) , board_id

the runing totals are not coming out right, see image below ( i modified the dates but that didn't affect the query)

Select Convert( varchar(20),datestamp,101) as datestamp, board_id,
    Sum(actual_qty), Sum(target_qty),
    (select sum(ta2.actual_qty) from tb_actual ta2
     where ta2.board_id = ta.board_id
     and datestamp >=dateadd(m, datediff(m, 0, getdate()),0)
     and datestamp <=getdate()
     and ta2.datestamp <= Convert( varchar(20),ta.datestamp,101)) AS running_actual,
    (select sum(ta2.target_qty) from tb_actual ta2
     where ta2.board_id = ta.board_id
     and datestamp >=dateadd(m, datediff(m, 0, getdate()),0)
     and datestamp <=getdate()
     and ta2.datestamp <= Convert( varchar(20),ta.datestamp,101)) AS running_target
from TB_Actual ta
WHERE datestamp >=dateadd(m, datediff(m, 0, getdate()),0)
and datestamp <=getdate()
and board_id='4'
Group by Convert( varchar(20),datestamp,101) , board_id
order by Convert( varchar(20),datestamp,101) , board_id
running-total1.jpg

This will give you the running totals from '02/04/2009' to current date. check whether you are getting correct running totals or not and let me know.
Quesiton - Do you have records for '01/02/2009' to '01/03/2009' in your table?

SELECT CONVERT( varchar(20),t1.datestamp,101) DateStamp, 
       board_id, 
       SUM(t1.actual_qty) as actual_qty_total, 
       SUM(t1.target_qty) as target_qty_total,
       (SELECT SUM(t2.actual_qty) 
          FROM TB_Actual t2
         WHERE CONVERT( varchar(20),t2.datestamp,101) <= CONVERT( varchar(20),t1.datestamp,101)
           AND t2.datestamp >'02/04/2009'
           AND t1.board_id = t2.board_id) AS running_actual,
        (SELECT SUM(t3.target_qty) 
          FROM TB_Actual t3
         WHERE CONVERT( varchar(20),t3.datestamp,101) <= CONVERT( varchar(20),t1.datestamp,101)
           AND t3.datestamp >'02/04/2009'
           AND t1.board_id = t3.board_id) AS running_target
  FROM TB_Actual t1
 WHERE t1.datestamp >'02/04/2009'
   AND t1.datestamp <=getdate()
   AND t1.board_id='4'
 GROUP BY CONVERT( varchar(20),t1.datestamp,101) , t1.board_id
order by Convert( varchar(20),t1.datestamp,101) , t1.board_id

Select allOpen in new window

>> the runing totals are not coming out right <<

D'OH, quite right, I should have noticed that ... since the date has been changed from a datetime to a *character* 'mm/dd/yyyy', the "<=" comparison no longer works correctly.

Rats ... I think to make this work you will have to delay converting the date to a more user-friendly format until after the totals are computed.

For example:


Select convert(varchar(20),datestamp,101), board_id,
    actual_qty_sum, target_qty_sum, running_actual, running_target
From (
Select dateadd(day, datediff(day, 0, datestamp),0) as datestamp, board_id,
    Sum(actual_qty) as actual_qty_sum, Sum(target_qty) as target_qty_sum,
    (select sum(ta2.actual_qty) from tb_actual ta2
     where ta2.board_id = ta.board_id
     and datestamp >=dateadd(m, datediff(m, 0, getdate()),0)
     and datestamp <=getdate()
     and ta2.datestamp <= dateadd(day, datediff(day, 0, ta.datestamp),0)) AS running_actual,
    (select sum(ta2.target_qty) from tb_actual ta2
     where ta2.board_id = ta.board_id
     and datestamp >=dateadd(m, datediff(m, 0, getdate()),0)
     and datestamp <=getdate()
     and ta2.datestamp <= dateadd(day, datediff(day, 0, ta.datestamp),0)) AS running_target
from TB_Actual ta
WHERE datestamp >= dateadd(m, datediff(m, 0, getdate()),0)
and datestamp <= getdate()
and board_id='4'
Group by dateadd(day, datediff(day, 0, datestamp),0) , board_id
) as derived
order by 1, 2

sorry the running totals are coming out wrong, the same numbers as above,.... it will b e intersting for me to learn how to do it this way, although someone figured out how to do this using temp tables...

The numbers came out fine for me ... please re-verify that you are using my latest version of the query. [Posted after the text "For example:" at 02.12.2009 at 02:11PM *C*ST]

scott, here are the results for your query
runningtotal2.jpg

yours worked perfectly !

thanks everyone for contributing, this is a major help for the problem i was having.

glad that it helped you.

Lol, he gets all the points for copying *my* original query and making minor mods.

You got that result from running this??:

Select convert(varchar(20),datestamp,101), board_id,
    actual_qty_sum, target_qty_sum, running_actual, running_target
From (
Select dateadd(day, datediff(day, 0, datestamp),0) as datestamp, board_id,
    Sum(actual_qty) as actual_qty_sum, Sum(target_qty) as target_qty_sum,
    (select sum(ta2.actual_qty) from tb_actual ta2
     where ta2.board_id = ta.board_id
     and datestamp >=dateadd(m, datediff(m, 0, getdate()),0)
     and datestamp <=getdate()
     and ta2.datestamp <= dateadd(day, datediff(day, 0, ta.datestamp),0)) AS running_actual,
    (select sum(ta2.target_qty) from tb_actual ta2
     where ta2.board_id = ta.board_id
     and datestamp >=dateadd(m, datediff(m, 0, getdate()),0)
     and datestamp <=getdate()
     and ta2.datestamp <= dateadd(day, datediff(day, 0, ta.datestamp),0)) AS running_target
from TB_Actual ta
WHERE datestamp >= dateadd(m, datediff(m, 0, getdate()),0)
and datestamp <= getdate()
and board_id='4'
Group by dateadd(day, datediff(day, 0, datestamp),0) , board_id
) as derived
order by 1, 2

Very odd.

ScottPletcher - I didn't copy your query. After the post from Author (ID: 23625642) I started working on this problem by creating a temp table in my machine with the sample data provided by the author. After clicking on submit button only, i realised that someone else also commented on this question ( I didn't refresh the page).

Ok.  I don't think yours will work properly either for some common situations, but whatever.

Post those common situations. I have no problems in modifying my query if required.

sorry I should have been more fair in distributing points.

i have a new question related to this answer, should i open another question?

In general you will need to open another q. In general EE's policy is that truly different qs require separate qs being asked.

However, if the q is a reasonable follow on to this q, it can simply be asked here. [Be aware, though, that some responders will follow up on qs that they have already received pts for, but some do not, and the level of "after" support varies.]

here is my new question,

https://www.experts-exchange.com/questions/24139657/Running-TOtals-query-help.html