Hi

What about this

r.dbPatID, t.PatientFirstName, t.patientlastname, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName,

SUM(CASE WHEN t.LedgerAmount > 0 AND t.LedgerType != 1 THEN t.LedgerAmount ELSE 0.00 END) AS Charges,

SUM(CASE WHEN t.LedgerAmount < 0 AND t.LedgerType != 1 THEN t.LedgerAmount ELSE 0.00 END) AS Payments

FROM

TESTReferralKPIs r

LEFT JOIN TESTTransactions t ON t.PatientID = r.dbPatID

GROUP BY

r.dbPatID, t.PatientFirstName, t.patientlastname, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName

--Output

dbPatIDPatientFirstNamepatientlastnamedbstatusdescdbAddDateLastNamedbStaffLastNameChargesPayments

90003212AnitaButtonInvisalign2013-04-18 10:04:59.000GoogleHodgson3725.0000-3725.0000

Regards,

Igor