How to create running totals with this query?

I have this table that collects data hourly on a 24 hourbasis. I created a query that will give me the daily totals,

Select Convert( varchar(20),datestamp,101), board_id, Sum(actual_qty), Sum(target_qty) from TB_Actual
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
it looks like this now, 
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
 
 
but i would like to modify the query so it displays records from 2/1-2/28 and add 2 columns for the running totals MTD, how do i do that?
 
 
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

Open in new window

sassy168Asked:
Who is Participating?
 
SharathData EngineerCommented:
Did you check my query? Are you getting wrong values for my query also?
0
 
brad2575Commented:
Here is a good article to do what you are looking for:

http://www.mssqltips.com/tip.asp?tip=1686
0
 
BrandonGalderisiCommented:
You should not be posting duplicate questions:

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

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24110414.html#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

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sassy168Author Commented:
sorry, i prefer to use queries instead of temp tables,
0
 
SharathData EngineerCommented:

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
0
 
sassy168Author Commented:
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.
0
 
SharathData EngineerCommented:
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)
0
 
sassy168Author Commented:
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?

0
 
Scott PletcherSenior DBACommented:
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
0
 
sassy168Author Commented:
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
0
 
SharathData EngineerCommented:

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

Open in new window

0
 
Scott PletcherSenior DBACommented:
>> 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.
0
 
Scott PletcherSenior DBACommented:
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
0
 
sassy168Author Commented:
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...
0
 
Scott PletcherSenior DBACommented:
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]
0
 
sassy168Author Commented:
scott, here are the results for your query
runningtotal2.jpg
0
 
sassy168Author Commented:
yours worked perfectly !
0
 
sassy168Author Commented:
thanks everyone for contributing, this is a major help for the problem i was having.
0
 
SharathData EngineerCommented:
glad that it helped you.
0
 
Scott PletcherSenior DBACommented:
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.
0
 
SharathData EngineerCommented:
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).
0
 
Scott PletcherSenior DBACommented:
Ok.  I don't think yours will work properly either for some common situations, but whatever.
0
 
SharathData EngineerCommented:
Post those common situations. I have no problems in modifying my query if required.
0
 
sassy168Author Commented:
sorry I should have been more fair in distributing points.

i have a new question related to this answer, should i open another question?
0
 
Scott PletcherSenior DBACommented:
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.]
0
 
sassy168Author Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.