"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
>
>
>
>
>
>

"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...
> > 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
> >
> >
> >
> >
> >
> >
>
>

"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
>
>
>
>
>
>

"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
> 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
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

"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
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>