Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

sql server query running total

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
johnnyg123
Asked:
johnnyg123
  • 2
  • 2
  • 2
1 Solution
 
dannygonzalez09Commented:
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
 
johnnyg123Author Commented:
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
 
dannygonzalez09Commented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
ZberteocCommented:
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
 
ZberteocCommented:
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
 
johnnyg123Author Commented:
perfect....Thanks!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now