find missing bills

[wfd1bdb]

Here is the issue:
I have 166 accounts (I shortened the account list for this post to 6 and the cpt code list from 35 to 5)and need to find records where there is a note (Modality) but no bill (CLcharge). In other words, instances where our organization has provided a service BUT that service was not billed. The code below finds records of the 166 where there is both a bill and a note to accompany that bill. When I run this script it works but gives me 61000+ records with both bills and notes. I need only records where there are only notes but no bills. Anyhelpful advice would be appreciated.



SELECT A.COMPANY, B.PLNAME, B.PFNAME, A.XACDATE2, A.CUNITS, A.PROCDESC, A.PROVIDER
FROM CLCHARGE AS A INNER JOIN
CLMASTER AS B ON A.COMPANY = B.COMPANY AND A.ACCOUNT = B.ACCOUNT
WHERE A.CHGAMOUNT - ISNULL(A.ADJUST, .0000) <> 0 AND A.CPT IN ('120', '121', '122', '123', '130')
AND (A.ACCOUNT (IN '610146', '603194', '402902', '681377', '514646', '706572'))
AND a.XACDATE2 BETWEEN '10-01-2002' AND '12-31-2003'

UNION

SELECT C.COMPANY, D.PLNAME, D.PFNAME, C.REPORTED, C.MINUTES, C.MODALITY ,C.USERCODE
FROM CLPSYCHENCOUNTER AS C INNER JOIN
CLMASTER AS D ON C.COMPANY = D .COMPANY AND C.SSNO = D .ACCOUNT
where c.ssno in ('610146', '603194', '402902', '681377', '514646', '706572')
AND c.reported BETWEEN '10-01-2002' AND '12-31-2003'

 

[bborissov]

Which table (or select) gives you notes and which bills?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[wfd1bdb]

the 1st Select gives BILLS = A.PROCDESC
the 2nd Select gives NOTES = C.MODALITY

 

[bborissov]

SELECT * FROM 
      CLPSYCHENCOUNTER CLP
LEFT JOIN CLCHARGE ON Clp.Company = CLCHARGE.Company AND
                      Clp.SSNO    = CLCHARGE.ACCOUNT
WHERE CLCHARGE IS NULL -- put any additional conditions here

(I hope I gues right the relation between two tables)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.