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: 289285 # Views: 40 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Thursday, December 30, 2010 10:19:17 AM         
   


> Setting the Patinfo relationship into bill seems to solve the problem of the patinfo (cus)
> name problem. I did it as the second join.
>
> What do you think?
>
>
>
>
> CLOSE DATABASE
> SET PATH TO (HOME(8) + 'Data\')
> OPEN DATABASE OIMA1
> USE patinfo IN 0
> USE BILLDET IN 0
> USE PATBILL IN 0
> USE PATPAY IN 0
>
> Select cus.name, cus.street, cus.city, bill.name,bill.billnumber, bill.dateservc, hasDebt.charges, hasDebt.amountpaid, hasDebt.adjustment ;
> from patbill bill ;
> inner Join ;
> ( Select detail.billnumber, charges, adjustment, amountpaid From ;
> ( ;
> Select ;
> billnumber, ;
> SUM(charges) as charges ;
> From BILLDET ;
> Group By billnumber ;
> ) Detail ;
> left Join ;
> ( ;
> Select ;
> billnumber, ;
> SUM(amountpaid) as amountpaid, ;
> SUM(adjustment) as adjustment ;
> From PATPAY ;
> Group By billnumber ;
> ) Payment ;
> on Detail.billnumber = Payment.billnumber ;
> where Detail.charges > 0 And ;
> (Payment.amountpaid Is Null Or Detail.charges > Payment.amountpaid+Payment.adjustment) ;
> ) hasDebt ;
> on bill.billnumber = hasDebt.billnumber ;
> INNER Join patinfo cus On cus.patnum = bill.patnum ;
> where bill.dateservc < Gomonth(Date(),-1)

Looks fine to me.

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