[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to create running totals with this query?

Posted on 2009-02-12
26
Medium Priority
?
286 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

0
Comment
Question by:sassy168
  • 10
  • 7
  • 7
  • +2
26 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 23624616
Here is a good article to do what you are looking for:

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

Expert Comment

by:BrandonGalderisi
ID: 23624717
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
 

Author Comment

by:sassy168
ID: 23625326
sorry, i prefer to use queries instead of temp tables,
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 41

Expert Comment

by:Sharath
ID: 23625380

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
 

Author Comment

by:sassy168
ID: 23625479
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
 
LVL 41

Expert Comment

by:Sharath
ID: 23625508
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
 

Author Comment

by:sassy168
ID: 23625642
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23625748
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
 

Author Comment

by:sassy168
ID: 23625819
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
 
LVL 41

Expert Comment

by:Sharath
ID: 23625925

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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23626059
>> 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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23626170
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
 

Author Comment

by:sassy168
ID: 23627020
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
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 23627097
Did you check my query? Are you getting wrong values for my query also?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23627114
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
 

Author Comment

by:sassy168
ID: 23627483
scott, here are the results for your query
runningtotal2.jpg
0
 

Author Closing Comment

by:sassy168
ID: 31546193
yours worked perfectly !
0
 

Author Comment

by:sassy168
ID: 23627564
thanks everyone for contributing, this is a major help for the problem i was having.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 23627592
glad that it helped you.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23627647
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
 
LVL 41

Expert Comment

by:Sharath
ID: 23627705
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23627787
Ok.  I don't think yours will work properly either for some common situations, but whatever.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 23627876
Post those common situations. I have no problems in modifying my query if required.
0
 

Author Comment

by:sassy168
ID: 23628273
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23628321
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question