Running sum and subquery - 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

General SQL Server Forums

New to SQL Server Programming

Running sum and subquery

Author

Topic

Mzane
Starting Member

7 Posts

Posted - 2007-11-08 : 05:18:07

I have a problem with getting the running sum for each month and year (month and year is one - YearAndMonth)

With the following code I allmost succeed retrieving the data, but some of the months are showing twice or more.

--------------------------------------
SELECT CustomerName
,YearAndMonth
,(

SELECT SUM([OriginalAmount]) 'Running'
FROM Fact_CustomerEntry
WHERE (Customer_ID = CE.Customer_ID AND Entry_Date_ID <= CE.Entry_Date_ID)

) AS 'Running Total'

FROM Fact_CustomerEntry CE
JOIN Dim_Customer ON CE.Customer_ID = Dim_Customer.Customer_ID
JOIN Dim_Date ON CE.Entry_Date_ID = Dim_Date.Date_ID
WHERE (CustomerNumber = '08243')
ORDER BY CustomerName
--------------------------------------

I tried a Group By on CustomerName and YearAndDate, but it fails on Customer_ID and Entry_Date_ID in the subquery.

Hope someone have the gift to help me.

Thanks

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-11-08 : 09:05:07

Can you post some sample data as well as table definition script?

Also I want to clear one thing out the way, when you said "running sum of each month", I assume you want to see a sum for monthe 1, and a sume for month 1 plus month 2, and a sum for month 1+2+3, and so forth. Am I correct?

Mzane
Starting Member

7 Posts

Posted - 2007-11-08 : 09:38:03

Hi & thanks

You are right about the running sum. First post shows the SUM for first month, second post shows the SUM for first + second month and so on. Just like you wrote.

Table "Dim_Date" contains "Date_ID" and "YearAndMonth" (shows a string like "2007 - 7 - July")

Table "Dim_Customer" contains "Customer_ID" and "CustomerName"

Table "Fact_CustomerEntry" contains "Customer_ID", "Entry_Date_ID" and "OriginalAmount". Where Customer_ID and Entry_Date_ID refers to the Customer and Date tables.

There are more OriginalAmounts per month, so thats why I get dobble posts.

I get something like this:

CUSTOMERNAME YEARANDMONTH ORIGINALAMOUNT (RUNNING SUM)
Customer A 2004 - 06 - June 8390,88
Customer A 2004 - 07 - July 8964,81
Customer A 2004 - 08 - August 8973,74
Customer A 2004 - 09 - September 9039,93
Customer A 2004 - 10 - October 14293,86
Customer A 2004 - 10 - October 14293,86
Customer A 2004 - 11 - November 8293,86
Customer A 2004 - 11 - November 8293,86
Customer A 2004 - 11 - November 8317,76
Customer A 2004 - 12 - December 8382,83

But what Im looking for is this:
CUSTOMERNAME YEARANDMONTH ORIGINALAMOUNT (RUNNING SUM)
Customer A 2004 - 06 - June 8390,88
Customer A 2004 - 07 - July 8964,81
Customer A 2004 - 08 - August 8973,74
Customer A 2004 - 09 - September 9039,93
Customer A 2004 - 10 - October 14293,86
Customer A 2004 - 11 - November 8317,76
Customer A 2004 - 12 - December 8382,83

I hope this spreads some light over my problem?

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-11-08 : 11:16:49

Ok, and is this on sql05 or sql2k?

If it's 05, there are at least two ways to do it. Cursor and set base. Cursor way of doing it only scan the data once while set base has to rescan same set of data mre than once.

So, to point you to the right direction, could you tell us the performance ralated info?

Mzane
Starting Member

7 Posts

Posted - 2007-11-08 : 12:11:20

Im pretty sure Im on a SQL2k.
There is a lot of data, and best perfomance would be super.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-11-08 : 14:49:13

Take a look of this.

USE tempdb;
go
if object_ID('dbo.Dim_date') is not null drop table dbo.Dim_Date;
go

CREATE Table dbo.Dim_Date
(Date_ID INT,
YearAndMonth datetime
);
--select dateadd(m, -1, getDate())

INSERT into dbo.Dim_Date (Date_ID, YearAndMonth)
Values (1,dateadd(m, -2, getDate()))
INSERT into dbo.Dim_Date (Date_ID, YearAndMonth)
Values (2,dateadd(m, -1, getDate()))
INSERT into dbo.Dim_Date (Date_ID, YearAndMonth)
Values (3,getDate())

if object_ID('dbo.Dim_Customer') is not null drop table dbo.Dim_Customer;
go

CREATE Table dbo.Dim_Customer
(Customer_ID varchar(20),
CustomerName varchar(20)
);

INSERT into dbo.Dim_Customer (Customer_ID, CustomerName)
Values ('08243','Cust A')

if object_ID('dbo.Fact_CustomerEntry') is not null drop table dbo.Fact_CustomerEntry;
go

CREATE Table dbo.Fact_CustomerEntry
(Customer_ID varchar(20),
Entry_Date_ID varchar(20),
OriginalAmount INT
);

INSERT into dbo.Fact_CustomerEntry (Customer_ID, Entry_Date_ID, OriginalAmount)
Values ('08243',dateadd(m, -2, getDate()), 10)
INSERT into dbo.Fact_CustomerEntry (Customer_ID, Entry_Date_ID, OriginalAmount)
Values ('08243',dateadd(m, -2, getDate()), 20)
INSERT into dbo.Fact_CustomerEntry (Customer_ID, Entry_Date_ID, OriginalAmount)
Values ('08243',dateadd(m, -1, getDate()), 30)
INSERT into dbo.Fact_CustomerEntry (Customer_ID, Entry_Date_ID, OriginalAmount)
Values ('08243',dateadd(m, -1, getDate()), 40)
INSERT into dbo.Fact_CustomerEntry (Customer_ID, Entry_Date_ID, OriginalAmount)
Values ('08243',dateadd(m, 0, getDate()), 50)

SELECT DC.CustomerName
,YearAndMonth
,(
SELECT SUM([OriginalAmount]) 'Running'
FROM Fact_CustomerEntry
WHERE (Customer_ID = CE.Customer_ID AND Entry_Date_ID <= CE.Entry_Date_ID)
) AS 'Running Total'

FROM Fact_CustomerEntry CE
JOIN Dim_Customer DC ON CE.Customer_ID = DC.Customer_ID
JOIN Dim_Date DD ON CE.Entry_Date_ID = DD.Date_ID
WHERE (DC.Customer_ID = '08243')
ORDER BY DC.CustomerName

IF OBJECT_ID( 'tempdb..#tmp' ) IS NOT NULL DROP TABLE #tmp
Select Entry_Date_ID, SUM(OriginalAmount) as OriginalAmount
into #tmp
from Fact_CustomerEntry group by Entry_Date_ID ORDER BY Entry_Date_ID desc

Select * from Fact_CustomerEntry

SELECT Entry_Date_ID,
OriginalAmount,
OriginalAmount + COALESCE((SELECT SUM(OriginalAmount)
FROM #tmp b
WHERE b.Entry_Date_ID > a.Entry_Date_ID),0)
AS RunningTotal
FROM #tmp a
ORDER BY Entry_Date_ID desc

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-11-08 : 14:51:46

Oops, please ignore the block of code that I copied from your post (the Select statement part) from my reply.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-08 : 15:34:40

It is always most efficient and easiest to simply calculate running totals at the presentation layer, as long as you don't need those running totals within the Database itself for further storage/calculation.

Mzane -- what are you ultimately doing with this calculation? Outputting to a report, or a web page, or a client application, or something else? If you provide specifics, it is easier to give you the best solution for your needs. 9 times out of 10 it is not something that should be done within SQL ....

- Jeff
http://weblogs.sqlteam.com/JeffS

Mzane
Starting Member

7 Posts

Posted - 2007-11-08 : 17:11:14

Hi guys and thanks until now... and for your work Hommer.

Maybe I should have mentioned it before, but as jsmith8858 ask for, Im trying to get the data from the tables to be used for a report.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-08 : 19:10:52

what reporting tool are you using? Almost all of them can provide runnings totals very easily, usually just as simple as Insert->running total and then entering some properties.

- Jeff
http://weblogs.sqlteam.com/JeffS

Mzane
Starting Member

7 Posts

Posted - 2007-11-09 : 00:15:25

Im sorry not to be better explaining. But for startes I only need the SQL query to give the above mentioned output, from here I may still have som changes. But it must be possible to get that output with a SELECT query with the use of SUM and ect.?

Im glad for your help and patience. Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-09 : 10:24:00

Again, where are you outputting these results? What specific type of client application?

- Jeff
http://weblogs.sqlteam.com/JeffS

Mzane
Starting Member

7 Posts

Posted - 2007-11-09 : 14:28:47

Output could be in a webpage, but currently just when I press "Execute" in my SQL Server manager

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-09 : 15:11:02

I give up ... good luck !!

- Jeff
http://weblogs.sqlteam.com/JeffS

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-09 : 15:37:39

so Mzane... why exactly don't you want to implement this in the front end?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Mzane
Starting Member

7 Posts

Posted - 2007-11-09 : 16:35:07

Jeff: I understand - Thanks for trying

At first I dont want to implement the results anywhere, when I get the right results I can do with them as I please.
So from the above mentioned tables I was hoping to retrieve the running sum pr. month using a SQL query. But my query is crap and looking for help to build a correct one... thats all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources