Solved

sql server query running total

Posted on 2013-12-10
6
434 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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
Managing Columnstore Indexes 2 27
Rename SQL Instance/SQL Developer Edition 2012 2 18
Correct Thousand and decimal separator in sql server 2008 3 25
sql help 8 55
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

813 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

15 Experts available now in Live!

Get 1:1 Help Now