Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 To: Gerald Fay
  Where is Gerald Fay?
 Olympia
 Washington - United States
 Gerald Fay
 Tags
Subject: RE: Selecting Records for Monthly Statement
Thread ID: 289106 Message ID: 289107 # Views: 80 # Ratings: 2
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Tuesday, December 28, 2010 11:43:41 AM         
   


> Service Industry program
>
> Database tables include
>
> Customerinf
> Billinf (Services)
> Detinf (Service details)
> Paymentinf
>
> Every Billinf may have one or more services in Detinf (one to many) a unique billnumber is the relationship field as is DateServc field
>
> Paymentinf list payments for the unique billnumber (many to one)
>
> Customerinf is connected to all by a custnum field
>
> Did not design Billinf to keep total for specific service as it can be summed from Detinf for that billnumber
>
> Problem:
> I need to select customers to receive a statement who have services that are over one month old and have unpaid services. The output should be ordered by CUSTOMER.
>
> Old fashion foxpro would be to sum each deinf billnumber to a memvar, open paymentinf and sum each payment for the billnumber to a memvar, then mark a field in the billinf to send a statement if not paid.
>
> Its clear that both detinf and paymentinf are both joined to Billinf by billnumber.
>
> What would the Sql statement look like to create a cursor for the statement report?
>
> Thanks.

You can shape your SQL around 'old fashion'. What you need to keep in mind is that, if you are going to sum something you need to do it separately. Otherwise, due to joins an amount may be summed N times. Something like this:

select cus.*, bill.BillDate, hasDebt.amount, hasDebt.payAmount ;
from customerInf cus ;
inner join billInf bill on cus.customerID = bill.customerID ;
inner join ;
( select billId, amount, payAmount from ;
  (select billID, sum(amount) as amount from deInf group by billID) detail ;
 left join ;
  (select billID, sum(payAmount) as payAmount from payments group by billID) payment ;
 on detail.billID = payment.billID ;
 where detail.Amount > 0 and (payment.payAmount is null or detail.Amount > payment.payAmount) ) hasDebt ;
on bill.billID = hasDebt.billID ;
where bill.dueDate < gomonth(date(),-1)


PS: Note that above SQL is off the top of my head w/o even knowing actual structures. Write and thoroughly test inner SQLs first and then build step by step IMHO.

Cetin Basoz

.Net has got better.Think about moving - check my blog:
Blog (main)
Blog (mirror)

ENTIRE THREAD

Selecting Records for Monthly Statement Posted by Gerald Fay @ 12/28/2010 11:10:02 AM
RE: Selecting Records for Monthly Statement Posted by Cetin Basoz @ 12/28/2010 11:43:41 AM
RE: Selecting Records for Monthly Statement Posted by Gerald Fay @ 12/29/2010 2:49:48 AM
RE: Selecting Records for Monthly Statement Posted by Cetin Basoz @ 12/29/2010 10:27:52 AM
RE: Selecting Records for Monthly Statement Posted by Gerald Fay @ 12/30/2010 6:48:05 AM
RE: Selecting Records for Monthly Statement Posted by Cetin Basoz @ 12/30/2010 9:05:44 AM
RE: Selecting Records for Monthly Statement Posted by Gerald Fay @ 12/30/2010 9:47:33 AM
RE: Selecting Records for Monthly Statement Posted by Cetin Basoz @ 12/30/2010 10:19:17 AM
RE: Selecting Records for Monthly Statement Posted by Gerald Fay @ 1/1/2011 7:21:04 PM
RE: Selecting Records for Monthly Statement Posted by Gerald Fay @ 1/2/2011 10:40:56 AM