Summing records and comparing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Summing records and comparing

Author

Topic

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2010-11-10 : 09:31:57

The table i have looks like this

Col1 Date Type Money Money2
A 1/1/10 Z 0.75 0.75
A 2/1/10 X 0.03 0.78
A 3/1/10 X 0.01 0.79
A 4/1/10 X 0.02 0.81
B 1/1/10 Z 0.61 0.61
B 2/1/10 X 0.01 0.62
B 3/1/10 X 0.04 0.66
B 4/1/10 X 0.02 0.83

This is quite hard to explain in words but i will try.
Eventually i want the Money column to look like Money2.
On each date there is a different money value which adds to the base value (shown as type z).
So for the next date i would like the value to be added to the previous dates value.

Any help would be much appreciated.

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 09:37:18

Looks like you need running total.
http://www.mssqltips.com/tip.asp?tip=1686

PBUH

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2010-11-10 : 10:17:43

I am trying to do the running total but it is totalling the whole column. This is the SQL i have tried.

SELECT TOP (100) PERCENT Col1, CONVERT(char(10), Date, 101) AS 'Date', Money,
(SELECT SUM(Money) AS Expr1
FROM dbo.[NAS_Inv fees]) AS 'Running Total'
FROM dbo.[NAS_Inv fees] AS [NAS_Inv fees_1]
ORDER BY col1

How can i get it to do a running total for each letter in col1.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-10 : 10:44:03

[code]SELECT TOP (100) PERCENT Col1, CONVERT(char(10), Date, 101) AS 'Date', Money,r.[Running Total]
FROM dbo.[NAS_Inv fees] AS [NAS_Inv fees_1]
CROSS APPLY(SELECT SUM(Money) AS [Running Total]
FROM dbo.[NAS_Inv fees]
WHERE Col1 = [NAS_Inv fees_1].Col1
AND Date <= [NAS_Inv fees_1].Date )r
ORDER BY col1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources