I am looking to report back visits with offsetting patient credits with patient debits. I want to first query all visits that have a patient credit balance. Then I want to see if the patient has an off-setting debit somewhere on another patientvisitId (this could be more then one visit). The balance must equal $0.00 for the patients visits to be pulled back into my report.Ultimately this is what I want to see back in my result set. Joe Patient - DOS 01/01/2009 - ($5.00) CreditJoe Patient - DOS 02/01/2009 - $2.50 DebitJoe Patient - DOS 04/01/2009 - $2.50 DebitNet result for Credits owed plus Debits owed = $0.00. This would enable my client to convey the funds from the credit visit(s) over to the visit(s) with the debit. Currently with the code below, I am getting this back in SQL:Msg 4104, Level 16, State 1, Line 1The multi-part identifier "ppa.PatBalance" could not be bound.WITH PPV AS ( SELECT pp.PatientId , pp.PatientProfileID , pv.PatientVisitId , pv.TicketNumber , ISNULL(CONVERT(VARCHAR(20) , pv.visit , 101) , '') AS [DateOfService] , pva.PatBalance FROM PatientVisit pv INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId ) SELECT PVCB.* , dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS [Patient Name] , ppa.PatBalance AS [Total Patient Balance] FROM ( SELECT * FROM PPV WHERE ppv . Patbalance < 0 ) AS PVCB --visits with credit bal INNER JOIN ( SELECT * FROM PPV WHERE ppv . Patbalance > 0 ) AS PVDB --visits with debit bal ON PVCB.PatientID = PVDB.PatientID INNER JOIN PatientProfile pp ON pp.PatientId = pvcb.PatientId
The multi-part identifier could not be bound
Posted - 2009-01-05 : 14:25:59
