Highest balance

Hi
I have table with all customers transactions
I am trying to create a query that can show me the balance of our customers.
I am trying to see when customer had the highest balance. How can I do this?
My customers transactions
Record number
Date.
Customer
Amount
21850
1.1.2004
1111
-1.699,85
21851
1.1.2004
1111
-638,71
21852
1.1.2004
1111
-2.795,87
21853
1.1.2004
1111
144,21
21854
1.1.2004
1111
25.472,30
21855
1.1.2004
2222
2.501,91
21856
1.1.2004
2222
19.942,04
21857
1.1.2004
2222
1.518,95
SELECT Customer, Date, Amount
FROM YourTable
WHERE Amount =
(SELECT MAX(Amount)
FROM YourTable Y1
WHERE Y1.Customer = YourTable.Customer)
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
>
> I have table with all customers transactions
> I am trying to create a query that can show me the balance of our
customers.
> I am trying to see when customer had the highest balance. How can I do
this?
>
> My customers transactions
>
> Record number
> Date.
> Customer
> Amount
> 21850
> 1.1.2004
> 1111
> -1.699,85
> 21851
> 1.1.2004
> 1111
> -638,71
> 21852
> 1.1.2004
> 1111
> -2.795,87
> 21853
> 1.1.2004
> 1111
> 144,21
> 21854
> 1.1.2004
> 1111
> 25.472,30
> 21855
> 1.1.2004
> 2222
> 2.501,91
> 21856
> 1.1.2004
> 2222
> 19.942,04
> 21857
> 1.1.2004
> 2222
> 1.518,95
>
>
>
|||Thank you for your answer Adam, but this did not work for me. Maybe I didn't
explain this right. I am not trying to get the highest amount from the
Column "Amount". I am trying to get the highest balance. Maybe I need to
create Column balance and calculate from Amount. Is that possible?
Example:
In this example i am trying to get the amount of 7000 that is the highest
balance for this customer
Record Date Customer Amount
1 01.01.04 3344 5000
2 01.01.04 3344 2000
3 01.01.04 3344 -1000
4 01.01.04 3344 -500
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
>
> I have table with all customers transactions
> I am trying to create a query that can show me the balance of our
customers.
> I am trying to see when customer had the highest balance. How can I do
this?
>
> My customers transactions
>
> Record number
> Date.
> Customer
> Amount
> 21850
> 1.1.2004
> 1111
> -1.699,85
> 21851
> 1.1.2004
> 1111
> -638,71
> 21852
> 1.1.2004
> 1111
> -2.795,87
> 21853
> 1.1.2004
> 1111
> 144,21
> 21854
> 1.1.2004
> 1111
> 25.472,30
> 21855
> 1.1.2004
> 2222
> 2.501,91
> 21856
> 1.1.2004
> 2222
> 19.942,04
> 21857
> 1.1.2004
> 2222
> 1.518,95
>
>
>
|||Ahh, now I understand...
The first step is to calculate a running balance:
SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
GROUP BY Tbl1.Customer, Tbl1.Record
Then we can use this as a derived table in an outer query to get the max per
customer... I've also added the date to the outer part of the query in case
you want that:
SELECT YourTable.Customer, YourTable.Date, MAX(Tbl0.Balance) As Balance
FROM YourTable
JOIN
(SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
GROUP BY Tbl1.Customer, Tbl1.Record
) Tbl0(Balance, Customer, Record) ON Tbl0.Record = YourTable.Record
GROUP BY YourTable.Customer, YourTable.Date
Note, I've used Tbl2.Record in order to determine the order of transactions;
if possible, you should use the date instead. I didn't, as the dates you
provided were non-unique.
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23bG$jZiUEHA.2580@.TK2MSFTNGP12.phx.gbl...
> Thank you for your answer Adam, but this did not work for me. Maybe I
didn't
> explain this right. I am not trying to get the highest amount from the
> Column "Amount". I am trying to get the highest balance. Maybe I need to
> create Column balance and calculate from Amount. Is that possible?
> Example:
> In this example i am trying to get the amount of 7000 that is the highest
> balance for this customer
> Record Date Customer Amount
> 1 01.01.04 3344 5000
> 2 01.01.04 3344 2000
> 3 01.01.04 3344 -1000
> 4 01.01.04 3344 -500
>
>
> "Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
> news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> customers.
> this?
>
|||Thank you Adam this works great you saved my day. Your first query "Running
balance" can I save this balance into my Customers transaction table? I did
create column called "running balance" in my Customers transaction table.
regards
Benedikt Fridbjornsson
Computer department
SIF Iceland
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OsGd0piUEHA.1172@.TK2MSFTNGP10.phx.gbl...
> Ahh, now I understand...
> The first step is to calculate a running balance:
> SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
> FROM YourTable Tbl1
> JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
> AND Tbl2.Record <= Tbl1.Record
> GROUP BY Tbl1.Customer, Tbl1.Record
>
> Then we can use this as a derived table in an outer query to get the max
per
> customer... I've also added the date to the outer part of the query in
case
> you want that:
> SELECT YourTable.Customer, YourTable.Date, MAX(Tbl0.Balance) As Balance
> FROM YourTable
> JOIN
> (SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
> FROM YourTable Tbl1
> JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
> AND Tbl2.Record <= Tbl1.Record
> GROUP BY Tbl1.Customer, Tbl1.Record
> ) Tbl0(Balance, Customer, Record) ON Tbl0.Record = YourTable.Record
> GROUP BY YourTable.Customer, YourTable.Date
>
> Note, I've used Tbl2.Record in order to determine the order of
transactions;[vbcol=seagreen]
> if possible, you should use the date instead. I didn't, as the dates you
> provided were non-unique.
>
> "Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
> news:%23bG$jZiUEHA.2580@.TK2MSFTNGP12.phx.gbl...
> didn't
highest[vbcol=seagreen]
5000[vbcol=seagreen]
2000[vbcol=seagreen]
-1000[vbcol=seagreen]
-500
>
|||Of course...
UPDATE YourTable
SET RunningBalance =
(SELECT SUM(Tbl2.Amount)
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
AND Tbl1.Record = YourTable.Record
GROUP BY Tbl1.Customer, Tbl1.Record)
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:uBVKsurUEHA.2668@.TK2MSFTNGP10.phx.gbl...
> Thank you Adam this works great you saved my day. Your first query
"Running
> balance" can I save this balance into my Customers transaction table? I
did[vbcol=seagreen]
> create column called "running balance" in my Customers transaction table.
> regards
> Benedikt Fridbjornsson
> Computer department
> SIF Iceland
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:OsGd0piUEHA.1172@.TK2MSFTNGP10.phx.gbl...
> per
> case
> transactions;
you[vbcol=seagreen]
to[vbcol=seagreen]
> highest
> 5000
> 2000
> -1000
> -500
do
>