optimizing SUM and CASE statements

  • 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