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: 289200 # Views: 50 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Wednesday, December 29, 2010 10:27:52 AM         
   


> Thanks for you suggestions, but having trouble getting this to work properly.
>
> The exact names of the tables and important fields are as follows
>
> patinfo.dbf contains demographics (name,address, age, telephone, etc) also contains patnum which is the unique
> ID used across all databases to identify patient
>
> patbill is the services information file contains datesrvc, primaryinsurance, datepost,diagnoscode1-4,and importantly
> unique billnumber
>
> billdet is the services detail file contains datesrvc,datepost,procedcode,billnumber,charges
>
> patpay is the payment file contains datepost,payer,billnumber,amountpaid,adjustment
> complicating the patpay file is that some entries are just adjustments, not payments, but should still be
> included in the sum calculations so will need to include patpay.adjustment as well as patpay.amountpaid in the SUM.
>
> tried running this code, but fails with: "Billnumber is not unique and must be qualified". I am sure I messed up the conversion. I can confirm that the billnumbers in patbill are definitely unique, as it is a primary index.
> It is a numeric value. I have heard that indexes may have trouble with them. Would making it a character field
> alter anything?
>
>
> CLOSE DATABASE
> SET PATH TO (HOME(8) + 'Data\')
> OPEN DATABASE OIMA1
> ACTIVATE WINDOW VIEW
> USE patinfo IN 0
> USE BILLDET IN 0
> USE PATBILL IN 0
> USE PATPAY IN 0
> 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 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)

My bad in original SQL (just typed and sent w/o checking, sorry).

When you have a column that exists in more than one table with the same name, SQL must need to have a way to differentiate which one you mean. In cases like that you need to alias it. ie:

select cust.Cust_id, company, order_id ;
 from Customer cust ;
 left join Orders  on cust.Cust_id = orders.Cust_id


If it were "select cust_id, ..." then it wouldn't know that if you want the values from customer or orders' cust_id column.

In your SQL billNumber exist on both Detail and Payment (subqueries) and outer select had just billNumber. It would be Detail.BillNumber (might be Payment.BillNumber too, but we are using left join and the value might be null on some rows while would always have a value if we get from Detail). Here is the revised SQL:

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.

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