We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

How to create running totals with this query?

sassy168
sassy168 asked
on
Medium Priority
302 Views
Last Modified: 2012-05-06
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

Comment
Watch Question

Commented:
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.

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

Author

Commented:
sorry, i prefer to use queries instead of temp tables,
SharathData Engineer
CERTIFIED EXPERT

Commented:

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

Author

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.
SharathData Engineer
CERTIFIED EXPERT

Commented:
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)

Author

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?

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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

Author

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
SharathData Engineer
CERTIFIED EXPERT

Commented:

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

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
>> 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.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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

Author

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...
Data Engineer
CERTIFIED EXPERT
Commented:
Did you check my query? Are you getting wrong values for my query also?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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]

Author

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

Author

Commented:
yours worked perfectly !

Author

Commented:
thanks everyone for contributing, this is a major help for the problem i was having.
SharathData Engineer
CERTIFIED EXPERT

Commented:
glad that it helped you.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.
SharathData Engineer
CERTIFIED EXPERT

Commented:
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).
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Ok.  I don't think yours will work properly either for some common situations, but whatever.
SharathData Engineer
CERTIFIED EXPERT

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

Author

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?
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.]
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.