Running SUM

[Mzane]

Dear Experts,

I have 3 tables:

"Dim_Date"
Date_ID
MonthNumber (Month nr... ex. jan=1)
Year (ex. Year=2007)
Year&Month (Contains both year and name of month... ex. "2007 - July")

"Customer"
Customer_ID
CustomerNumber (Contains a customernr... ex. "08243")
CustomerName (Contains the Customers name... ex. "Barneys Carwash")

"Customer_Entry"
Entry_Date_ID
Customer_ID
OriginalAmount (Contains the amount)

What I am looking for is to get the running sum (OriginalAmount) for each month...

lets say the "Customer_Entry" tables contains the following:

Entry_Date_ID Customer_ID OriginalAmount
1 33 150
2 33 100
2 33 50
3 33 200
4 33 300
4 33 200
6 65 120
6 65 140
7 65 170

Then my goal is to get the following output, for a customer with a certain CustomerNumber (not ID):

CustomerName Year&Month RunningSum
Mortens Bilvask 2007 - Jan 150
Mortens Bilvask 2007 - Feb 300 (150 + 100 + 50)
Mortens Bilvask 2007 - Mar 500 (150 + 100 + 50 + 200)
Mortens Bilvask 2007 - Apr 1000 (150 + 100 + 50 + 200 + 300 + 200)

When I did my own attemps I was using a subquery. But I keep getting dobble posts because there are more than 1 amount for 1 Entry_Date_ID.

I hope one of you can come up with a solution?

Regards Mzane

 

[Mzane]

Please exchange "Mortens Bilvask" with "Barneys Carwash" in the bottom example.

Hope you didn't get too confused... sorry!

 

[fredericofonseca]

without doing the lookup on the other 2 tables to get name/day&month

select distinct customer_entry_date_id, entry_date_id, (select sum(originalamount) from customer_entry f2 where f2.entry_date_id <= f1.entry_date_id and f1.customer_entry_date_id = f2.customer_entry_date_id)
from customer_entry f1


output based on the data you supplied is
33 1 150
33 2 300
33 3 500
33 4 1000
65 6 260
65 7 430


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Mzane]

Thanks

But I need the lookup!

 

[fredericofonseca]

just add it to the query. easy enough for you to do it.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Mzane]

Hi again, your'e right about the joins. They are no problem. But your solution don't give the right output.
With your solution I get one post for each entry_date_id what the goal is, is to get one post for each month! There can be several entry_date_ID's in one month.

Take a look at my example again.
I apprecheate your help very much and hope you can handle a little more?

 

[fredericofonseca]

please post the sql you have. my output is the same as your original one (even though i had a type on it.customer_entry_date_id should be customer_id)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Mzane]

But I have nearly the same SQL as you:
-----------------
SELECT DISTINCT Dim_Customer.CustomerName, Dim_Date.YearAndMonth,
(SELECT SUM(OriginalAmount)
FROM Customer_Entry f2
WHERE f2.Entry_Date_ID <= f1.Entry_Date_ID AND f1.Customer_ID = f2.Customer_ID) AS 'Running Sum'
FROM Customer_Entry f1 INNER JOIN
Customer ON f1.Customer_ID = Customer.Customer_ID INNER JOIN
Dim_Date ON f1.Entry_Date_ID = Dim_Date.Date_ID

--------------------------------------

But as I mentioned I get dobble posts for months which have more than one Entry_Date_ID. The goal is to have ex. all OriginalAmount summed up for jan 2003, then next post is feb 2003 which should be all posts for feb 2003 AND jan 2003 summed up and so on...

 

[fredericofonseca]

Ok. if you run the sql without the joins do you get the correct results? if not then please post here the output (first 30 lines) along with the first records that match the customer_id and entry_date_id from table customer_entry.

The distinct is what makes the duplicated entries go away..., to there must be something else on your tables,because as I said, taking in consideration a table with your entries
1 33 150
2 33 100
2 33 50
3 33 200
4 33 300
4 33 200
6 65 120
6 65 140
7 65 170

the output is
33 1 150
33 2 300
33 3 500
33 4 1000
65 6 260
65 7 430

which is exactly what you asked for (apart from the using name instead of 33/65, and year&month instead of 1,2,3,4,6,7)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Mzane]

Hi again FredericoFonseca, (and other viewers... if any?)

I see what you are telling me. And you are right about that every Entry_Date_ID is unique in your query, but as I mentioned before my goal is to add all amounts for each month and there may be several entry_date_ids on a month.

Here is my SQL and output:

SELECT DISTINCT Customer_ID, Entry_Date_ID
, (
SELECT SUM(OriginalAmount)
FROM CustomerEntry f2
WHERE f2.Entry_Date_ID <= f1.Entry_Date_ID AND f1.Customer_ID = f2.Customer_ID
)
FROM CustomerEntry f1

Output

ID E_D_ID Amount
33 1 150
33 2 300
33 3 500
33 4 1000
65 6 260
65 7 430

Same as yours... but what if Entry_Date_ID 2 and 3 is a date in the same month... then these two posts should be added and shown as one post.

Ex. lets say that the Dim_Date table looks like this:

Date_ID YearAndMonth Year Month
1 "2007 - Jan" 2007 1
2 "2007 - Feb" 2007 2
3 "2007 - Feb" 2007 2
4 "2007 - Mar" 2007 3
5 "2007 - Apr" 2007 4
6 "2007 - Jan" 2007 1
7 "2007 - Jan" 2007 1

Then the output Im looking for should be like this:

ID YnM Amount
33 07-jan 150
33 07-feb 800
33 07-mar 1000
65 07-jan 690

Still Im very pleased for your effort in this. Thanks again.

 

[fredericofonseca]

ah. thats another story then.

select CustomerName, YearAndMonth, sum('running sum')
from (
SELECT DISTINCT Dim_Customer.CustomerName, Dim_Date.YearAndMonth,
(SELECT SUM(OriginalAmount)
FROM Customer_Entry f2
WHERE f2.Entry_Date_ID <= f1.Entry_Date_ID AND f1.Customer_ID = f2.Customer_ID) AS 'Running Sum'
FROM Customer_Entry f1 INNER JOIN
Customer ON f1.Customer_ID = Customer.Customer_ID INNER JOIN
Dim_Date ON f1.Entry_Date_ID = Dim_Date.Date_ID
) temp
group by CustomerName, YearAndMonth

Note if you are returning a huge amount of records then this might not be the best solution.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com