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?
johnnyg123Asked:
Who is Participating?
 
dannygonzalez09Connect With a Mentor Commented:
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
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.