Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Gerald Fay
  Where is Gerald Fay?
 Olympia
 Washington - United States
 Gerald Fay
 To: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 Tags
Subject: RE: Selecting Records for Monthly Statement
Thread ID: 289106 Message ID: 289275 # Views: 37 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Thursday, December 30, 2010 9:47:33 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)

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