Re: Highest balance

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Benedikt Fridbjornsson (benni_at_sif.is)
Date: 06/15/04 

Date: Tue, 15 Jun 2004 09:29:35 -0000

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;
> 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
                -1000
> > 4 01.01.04
                -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
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>

Quantcast