sorry yeah, forgot the sql!

SELECT

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

(CASE WHEN t.LedgerType = 1 THEN t.LedgerAmount ELSE 0.00 END) * -1 AS Contracts

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, t.LedgerAmount