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: 289181 # Views: 45 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Wednesday, December 29, 2010 2:49:48 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)




> 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

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