May 27, 2015 at 12:57 pm
I looked at your query from a slightly different perspective. Not sure if this is going to be any better, but I'm joining to your transactions table 3 times instead of just once, based on the conditions specified. I'm not sure your conditions are correct, but you'll have to do any fixing of those specs. Here's my alternative, although it may not be any better:
SELECT r.dbPatID, p.dbPatFirstName, p.dbPatLastName, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName,
SUM(tc.LedgerAmount) AS Charges,
SUM(tp.LedgerAmount) AS Payments,
SUM(tr.LedgerAmount) * -1 AS Contracts
FROM vw_ReferralKPIs AS r
LEFT JOIN Transactions AS tc
ON r.dbPatID = tc.PatientID
AND r.ClientRef = tc.ClientRef
AND (
(tc.LedgerAmount > 0 AND tc.LedgerType NOT IN (1, 29, 30, 31))
OR
tc.LedgerType = 16
LEFT JOIN Transactions AS tp
ON r.dbPatID = tc.PatientID
AND r.ClientRef = tc.ClientRef
AND (
(tc.LedgerAmount < 0 AND tc.LedgerType NOT IN (1, 16, 45))
OR
tc.LedgerType IN (29, 30, 31)
LEFT JOIN Transactions AS tr
ON r.dbPatID = tc.PatientID
AND r.ClientRef = tc.ClientRef
AND tc.LedgerType IN (1, 46)
LEFT JOIN Patient AS p
ON p.dbPatID = r.dbPatID
AND r.ClientRef = p.ClientRef
WHERE r.dbAddDate >= '2014-01-01'
AND r.dbAddDate <= '2015-05-27 23:59'
--AND (r.dbStaffLastName IN ('') OR '' = '')
AND r.LastName IN ('Tio ')--OR 'Tio ' = '')
AND r.ClientRef = 'INV'
GROUP BY r.dbPatID, p.dbPatFirstName, p.dbPatLastName, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName
Note that I've eliminated one of the WHERE clause conditions because it's OR portion makes it irrelevant. I also took out the excess conditions which cannot evaluate to true. Let me know...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply