Post reply

create rows even if no inner join match

  • December 23, 2013 at 7:07 am

    #286497

    Hi,

    I have this sql...

    SELECT EPSReferralKPIs.dbPatID, EPSReferralKPIs.dbPatLastName, EPSReferralKPIs.dbAddDate, EPSReferralKPIs.LastName,EPSReferralKPIs.dbStaffLastName,

    SUM(epstransactions.LedgerAmount)as Outstanding,

    (SUM(CASE WHEN epstransactions.LedgerAmount <= 0 THEN epstransactions.LedgerAmount ELSE NULL END) * -1) as Charges,

    SUM(CASE WHEN epstransactions.LedgerAmount > 0 THEN epstransactions.LedgerAmount ELSE NULL END) as Payments

    FROM epstransactions

    INNER JOIN EPSReferralKPIs on epstransactions.PatientID = EPSReferralKPIs.dbPatID

    GROUP BY EPSReferralKPIs.dbPatID, EPSReferralKPIs.dbPatLastName, EPSReferralKPIs.dbAddDate, EPSReferralKPIs.LastName, EPSReferralKPIs.dbStaffLastName

    Which brings back any patient detail where the patientID (dbPatID) from the EPSRerreflKPIs table (names and dates) matches the patientID of the EPSTranscations table (amounts). Cool.

    What I would like to acheive is where there isn’t a join for the PatientID bewteen the tables to still create a row from the EPSReferralKPIs table (names and dates) and 0.00’s for the other columns (Outstanding, Charges, Payments).

    Thanks for any help.

  • December 23, 2013 at 7:39 am

    #1675607

    SELECT

    r.dbPatID,

    r.dbPatLastName,

    r.dbAddDate,

    r.LastName,

    r.dbStaffLastName,

    SUM(t.LedgerAmount) as Outstanding,

    (SUM(CASE WHEN t.LedgerAmount <= 0 THEN t.LedgerAmount ELSE NULL END) * -1) as Charges,

    SUM(CASE WHEN t.LedgerAmount > 0 THEN t.LedgerAmount ELSE NULL END) as Payments

    FROM EPSReferralKPIs r

    LEFT JOIN epstransactions t

    on t.PatientID = r.dbPatID

    GROUP BY r.dbPatID, r.dbPatLastName, r.dbAddDate, r.LastName, r.dbStaffLastName

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • December 23, 2013 at 8:08 am

    #1675621

    thank you...left join it is then.

  • December 23, 2013 at 8:14 am

    #1675625

    mattech06 (12/23/2013)

    thank you...left join it is then.

    Probably - it fits your verbal specification. But don't take it from me. Always test.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • December 23, 2013 at 8:18 am

    #1675627

    sorry, yep had already tested it.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply