--- In firebird-support@yahoogroups.com, "Martijn Tonies"
<m.tonies@...> wrote:

>
> > > > I have two tables:
> > > >
> > > > Customers (id, name)
> > > > History (cust_id, descript, in_money, out_money)
> > > >
> > > > Here is a sample data:
> > > >
> > > > Customers:
> > > > 1 Cust_Name1
> > > > 2 Cust_Name2
> > > > 3 Cust_Name3
> > > >
> > > > History
> > > > 1 Order No 01 0.00 100.00
> > > > 1 Order No 02 0.00 150.00
> > > > 1 Order No 03 0.00 180.00
> > > > 2 Order No 04 0.00 200.00
> > > > 2 Order No 05 0.00 250.00
> > > > 2 BankReport 1 200.00 0.00
> > > > 1 BankReport 2 150.00 0.00
> > > >
> > > > and I need SQL statement to get all order documents that are

not

> > > > complet payed.
> > > >
> > > > Cust_Name1 Order No 02 150.00 50.00
> > > > Cust_Name1 Order No 03 180.00 180.00
> > > > Cust_Name2 Order No 05 250.00 250.00
> > > >
> > > > Last column is value which is represen the unclosed order by
> > payment!
> > > >
> > > > If I wasn't complicated in my description that is my SQL

problem!

> > >
> > > Care to tell how you are deriving the values in your supposed
> > result?
> > >
> > > How do you get these values?
> > >
> >
> > Customers:
> > 1;Cust_Name1
> > 2;Cust_Name2
> > 3;Cust_Name3
> >
> > History
> > 1;Order No 01;0.00;100.00
> > 1;Order No 02;0.00;150.00
> > 1;Order No 03;0.00;180.00
> > 2;Order No 04;0.00;200.00
> > 2;Order No 05;0.00;250.00
> > 2;BankReport 1;200.00;0.00
> > 1;BankReport 2;150.00;0.00
> >
> > and I need SQL statement to get all order documents that are not
> > complet payed.
> >
> > Cust_Name1;Order No 02;150.00;50.00
> > Cust_Name1;Order No 03;180.00;180.00
> > Cust_Name2;Order No 05;250.00;250.00
> >
> > Thanks Martin for ulta fast answer. I was delimited fields into
> > tables that you can see what is the valiues for the fileds and

for a

> > result dataset: I have Cust_Name1 which have 3 orders with summ

430$

> > and Cust_Name2 which have 2 orders with summ 450$. So Cust_Name1

was

> > pay 200$ and Cust_Name2 150$, and for a Cust_Name1 firs order are
>
> I think you mixed up CustIDs there, given the data above.

cust_id is foreign key to table customers field id.

>
> > closed full, secound order are closed only for a 100$ and he have

a 50

> > $ to pay to close that order and 250 to close all orders.
>
> But how do you know that payment "BankReport 1" is for Order No 1

and 2?

>
> The customer might as well be paying for Order No 03 instead, and

Order 01

> and 02 are still open.

For closing orders I'm sort all orders by date and closing are going
from top to bottom until sum(payment) are <= 0 after that all other
orders are stil opened!

>
> > I think that is a little closer explanation! ;-)
>
>
> Martijn Tonies
> Database Workbench - development tool for Firebird and more!
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>