Solved

sql server query running total

Posted on 2013-12-10
6
435 Views
Last Modified: 2013-12-10
here is some sample data from a table named transHistory

date                      amount

1/1/2013 09:20    100
1/1/2013 09:25     300
1/1/2013 09:30     -100
1/12013  09:50      200
1/1/2013 10:00     -700


What I am trying to do is create a query that lists each time
and a cumulative total of  amount

Given the sample data above the result would be as follows


1/1/2013 09:20    100
1/1/2013 09:25     400
1/1/2013 09:30     300
1/12013  09:50     500
1/1/2013 10:00    -200

This query would store the data into a temp table so that I could then query
to determine which date time had the highest cumulative total

Given the above it would be

1/12013  09:50     500

I'm assuming a stored proc?
0
Comment
Question by:johnnyg123
  • 2
  • 2
  • 2
6 Comments
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 39709017
SELECT * INTO ##Temp
FROM
(
SELECT [Date]
		, Amount
		, Amount+COALESCE((SELECT SUM(Amount) FROM Amount b WHERE b.[Date] < a.[Date]),0) AS RunningTotal 
FROM Amount a ORDER BY [Date]
)X

SELECT TOP 1 * FROM ##Temp
ORDER BY Amount Desc

Open in new window


Give that a try
0
 

Author Comment

by:johnnyg123
ID: 39709053
Thanks so much for the post!!!

one thing I forgot to mention is that the table will have entries with multiple dates with different time entries.   I am interested in a given date regardless of time


For example

date                      amount

1/1/2013 09:20    100
1/1/2013 09:25     300
1/1/2013 09:30     -100
1/1/2013  09:50      200
1/1/2013 10:00     -700
1/2/2013 09:30    400
1/2/2013 10:25     300
1/2/2013 11:30     -200
1/2/2013  12:50      100
1/2/2013 13:00     -700

all I am interested in is for a given date...in the example above 1/1/2013

(but user may want 1/2/2013...etc)

Hope this makes sense
0
 
LVL 5

Accepted Solution

by:
dannygonzalez09 earned 500 total points
ID: 39709135
So you want the highest cumulative total for each date?

you can replace the inner select with the below SQL

SELECT [Date]
		, Amount
		, Amount+COALESCE((SELECT SUM(Amount) FROM ##Amount b WHERE b.[Date] < a.[Date] AND CONVERT(DATE,a.[Date]) = CONVERT(DATE,b.[Date])),0) AS RunningTotal 
FROM ##Amount a ORDER BY [Date]

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 26

Expert Comment

by:Zberteoc
ID: 39709282
Here:
-- query 1, cumulative amounts
select 
	dte,
	(select sum(amount) from transHistory where dte<=th.dte) as cumulative_amount
from
	transHistory th

-- query 2, max cumulative amount row
select top 1
	dte,
	(select sum(amount) from transHistory where dte<=th.dte) as max_cumulative_amount
from
	transHistory th
order by 
	2 desc

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39709314
Actually this;
-- query 1, cumulative amounts
select 
	cast(th.dte as date),
	(select sum(amount) from transHistory where dte<=th.dte) as cumulative_amount
from
	transHistory th
where
	th.dte = (select max(dte) from transHistory where cast(dte as date)=cast(th.dte as date))



-- query 2, max cumulative amount row
select top 1
	dte,
	(select sum(amount) from transHistory where dte<=th.dte) as max_cumulative_amount
from
	transHistory th
order by 
	2 desc

Open in new window

0
 

Author Closing Comment

by:johnnyg123
ID: 39709456
perfect....Thanks!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 57
Help Required 3 108
SQL DATEADD 10 71
Compare a column in results by values left of decimal 2 7
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

821 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