CODEWITHSUNDEEP
sqlsql-server-2008-r2left-joinwhere-clause
Jeremy McDevitt
Jeremy McDevitt

Reputation: 115

Using a Correlated Subquery within a Left Join

I need a fresh set of eyes on this query. Without getting mega in depth in this code my problem is I'm doing a left join to pull from the TXP_Digital_Signatures (tds) table which stores signatures to the most current version of Treatment Plans (txp_master txp). What this code is doing is bringing back results where tds.signed is null (no signature) or marked N (No). This works, but what this report has done is show people what No's need to become yes, but that is leaving the No left behind, so if there is a more recent Yes then the No in that version of the tds.plan_id it is still pulling that plan_id where I no longer want it where the most recent signature status is a Y (yes), etc. The code snippet below added to the where statement works, but it hides all No's even if there is not a newer Y (yes).

tds.date = (select Max(date) from TXP_Digital_Signatures where tds.plan_id = txp.plan_id)

Can anyone think of a way to either add a correlated subquery to the left join, so it only pulls the max(tds.date) for each tds.plan_id or how to rework my where statements so the no's without a newer yes and the null's still show up. I really don't want to redo the entire report as a grouped report if I can help it where I feel it'll break a ton of stuff on me and basically have me redoing this report from scratch. SQL 2008 R2

SELECT  case_status,
    CONVERT(CHAR(10), episode_open_date, 101)AS 'Enrolled' ,
    txp.patient_id, 
    p.lname+', ' + p.fname AS 'Client',
    CONVERT(CHAR(10), txp.effective_date, 101)AS 'Effective', 
    CONVERT(CHAR(10), next_review_date, 101)AS 'Review',
    txp.signed,
    (SELECT location_code FROM staff s WHERE s.staff_id = txp_coordinator_id) AS 'Clinic',
    (SELECT s.lname+', ' +s.fname FROM staff s WHERE s.staff_id = txp_coordinator_id) AS 'Coordinator',
    (SELECT s.lname+', ' +s.fname FROM staff s WHERE s.staff_id = ts.team_member_id ) AS 'Team', 
    ts.signed,
    tds.signed as 'Patient Sig'
FROM txp_master txp join patient p ON p.patient_id = txp.patient_id and p.episode_id = txp.episode_id
    join txp_signature ts on ts.plan_id = txp.plan_id and ts.version_no = txp.version_no and ts.team_member_id <> txp.txp_coordinator_id
    left join TXP_Digital_Signatures tds on tds.plan_id = txp.plan_id

where p.case_status = 'A' and 
txp.status <> 'er' and patient_signed_date is null 
and tds.signed is null or tds.signed = 'N'
and txp.effective_date > '2016-12-31 00:00:00.000'
and tds.date = (select Max(date) from TXP_Digital_Signatures where tds.plan_id = txp.plan_id)

order by patient_id

Upvotes: 0

Views: 2519

Answers (1)

Stephen
Stephen

Reputation: 1542

Your query should work if you correct the sub-query, like this:

(select Max(date) from TXP_Digital_Signatures x where x.plan_id = tds.plan_id)

currently, you are not filtering the sub-query TXP_Digital_Signatures.

One other thing to take note of is that you have a LEFT JOIN on TXP_Digital_Signatures tds yet you include it on the WHERE clause. This will convert it to an INNER JOIN. So decide on what join you require and change accordingly.

If you want results regardless of TXP_Digital_Signatures tds then move those conditions to the ON clause.

If you only want results based on TXP_Digital_Signatures tds then change to INNER JOIN

Upvotes: 2

Related Questions