Solved

sql server query running total

Posted on 2013-12-10
6
432 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 43
Help with simplifying SQL 6 51
Sql query 107 30
Sql Query with datetime 3 14
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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now