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: 289267 # Views: 39 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Thursday, December 30, 2010 6:48:05 AM         
   


Yes much better I can see the charges, amountpaid and adjustments are fine
The problem now is the patinfo (cus) file. It shows the same name in all rows, if I add bill.name to the
query as here:


Select cus.*,<B>bill.name</B>, bill.dateservc, hasDebt.charges, hasDebt.amountpaid, hasDept.adjustment

The name associated with charges, billnumber,amountpaid and adjustement is correct, while the cus.name never
changes.

Something is not right with the cus and patbill join.







> 
> Select cus.*, bill.dateservc, hasDebt.charges, hasDebt.amountpaid, hasDept.adjustment ;
> 	from patinfo cus ;
> 	inner Join PATBILL bill On cus.patnum = PATBILL.patnum ;
> 	inner Join ;
> 	( Select detail.billnumber, charges, amountpaid, adjustment 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 ;
> 	where bill.dateservc < Gomonth(Date(),-1)

>
>
> PS: When you have time check SampleDataToText.prg in FAQ section. Using that program you can directly send some data here as text. Using the ready made data, those who reply have a chance to write and test the code.
>
>

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