Subject | Re: Little complicated SQL request from guru |
---|---|

Author | majstoru |

Post date | 2007-05-28T12:02:02Z |

--- In firebird-support@yahoogroups.com, "Martijn Tonies"

<m.tonies@...> wrote:

from top to bottom until sum(payment) are <= 0 after that all other

orders are stil opened!

<m.tonies@...> wrote:

>not

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

> > > > complet payed.problem!

> > > >

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

> > >for a

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

> > result dataset: I have Cust_Name1 which have 3 orders with summ430$

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

> > pay 200$ and Cust_Name2 150$, and for a Cust_Name1 firs order arecust_id is foreign key to table customers field id.

>

> I think you mixed up CustIDs there, given the data above.

>a 50

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

> > $ to pay to close that order and 250 to close all orders.and 2?

>

> But how do you know that payment "BankReport 1" is for Order No 1

>Order 01

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

> 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

>