Recursive JOIN: table JOINed too GROUP BY of itself 1

[ProtocolPirate]

My Daysheet records have RecID, ChargeCode and PayID. The PayIDs refer back to a RecID and it is always only 1-too-many. However, there can be multiple billing records with the same ChargeCode, if more than one MRI/CT/XRay study was done on the same day. So ChargeCode too PayID is many-too-many.

So to get balances (charges-payments) I did a recursive JOIN from daysheet into a GROUP BY of itself on PayID. The SQL query below is selecting for a referring doctor with the code 'ZZ2676' that returns 9 records, that is, before JOINing to the 2nd reference too Dayshet that is a GROUP BY PayID. After JOINing too the GROUP BY PayID of Daysheet there are only 3 records, because there are only 3 records where the PayID matches a RecID (i.e. there were 3 payments on the charges in the 9 records).

I tried a LEFT OUTER JOIN and a RIGHT OUTER JOIN, but both ran forever and never returned. How do I get a balance for the 9 records and without having 2/3rds of my records truncated?


SELECT d.AcntNumber, d.Name, d.FromDate, d.Date, c.GrpName, c.CPT, c.Description, d.LocCode, p.PayTyp, d.ChargeLo/10000, dgrp.Adj/10000, dgrp.Pay/10000, d.ChargeLo/10000-dgrp.Adj/10000-dgrp.Pay/10000 AS Balance
FROM (SELECT ChargeNumber FROM Posting_Gardena where RefPhyCode='ZZ2676') AS a
INNER JOIN Daysheet d ON d.ChargeNum=a.ChargeNumber
INNER JOIN Charge c ON c.Id=d.ChargeID
INNER JOIN Patient p ON p.AcntNumber=d.AcntNumber
INNER JOIN (SELECT PayID, SUM(AdjLo) AS Adj, SUM(PymtLo) AS Pay
FROM Daysheet GROUP BY PayID) AS dgrp ON dgrp.PayID=d.RecID

 

[PHV]

Use a LEFT JOIN.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886