locked
Query help - possibly RANK() over partition and Pivot (maybe not) RRS feed

  • Question

  • This is just a sample and is not the actual table and data. The table cannot be changed.

    I need to select all patients whose latest CareType = ‘Attending’ where the DrID = 1372 (Daffy Duck). The latest is determined by the VisitDate. The DrID, Doctor, PatientID, and CareType are the columns selected. I also need to select the DrID as PrimaryID and Doctor as PrimaryDoctor for the latest CareType = ‘Primary Service Coordinator’ when it exists.

    Table name – Mess

    PK – MessID

    Sample data:

    MessID

    DrID

    Doctor

    PatientID

    CareType

    VisitDate

    5822

    1673

    Sylvester

    400

    Care Provider

    12/15/2013

    6082

    1146

    Tweety Bird

    400

    Primary Service Coordinator

    12/14/2013

    7108

    1597

    Bugs Bunny

    400

    Residential Provider

    12/1/2013

    8095

    1372

    Daffy Duck

    400

    Attending

    11/25/2013

    9290

    1456

    Yosemite Sam

    400

    Care Provider

    11/10/2013

    1

    1086

    Elmer Fudd

    500

    Licensed Professional

    12/15/2013

    567

    1557

    Porky Pig

    500

    Primary Service Coordinator

    12/10/2013

    595

    1607

    Foghorn Leghorn

    500

    Case Manager

    12/9/2013

    4911

    1012

    Wile E Coyote

    500

    Licensed Professional

    12/5/2013

    7453

    1148

    Roadrunner

    500

    Primary Service Coordinator

    12/5/2013

    10587

    1372

    Daffy Duck,

    500

    Attending

    11/20/2013

    10918

    1048

    Speedy Gonzales

    500

    Nurse

    11/15/2013

    12360

    1744

    Young, Robert

    500

    Attending

    11/12/2013

    10587

    1372

    Daffy Duck,

    600

    Attending

    11/20/2013

    17417

    1597

    Bugs Bunny

    600

    Attending

    11/10/2013

    17628

    1456

    Yosemite Sam

    600

    Care Provider

    11/4/2013

    Results:

    DrID

    Doctor

    PatientID

    CareType

    PrimaryID

    PrimaryDoctor

    1372

    Daffy Duck

    400

    Attending

    1146

    Tweety Bird

    1372

    Daffy Duck,

    500

    Attending

    1557

    Porky Pig

    1372

    Daffy Duck,

    600

    Attending

     

     

    The following will get the results for patients whose latest ‘Attending’ is DrID = 1372.

    Select * from
    (Select M.DrID, M.Doctor, M.PatientID, M.CareType
    RANK() over (partition by M.PatientID order by M.VisitDate desc) as latest
    from Mess  M
    Where M.DrID = ‘1372’
    And M.CareType = ‘Attending’) text
    Where latest = 1

    I have some ideas on how to build on that query but would like to see what others suggest. Feel free to fix or completely scrap the above query.

    Thanks



    Additional info: There will be patients in the table that will not have DrID = '1372' with CareType = 'Attending'. I should have included an entry for those cases in my sample. Those patients will not be part of the results.
    • Edited by DavidWIII Thursday, December 19, 2013 4:33 PM additional info
    Thursday, December 19, 2013 4:25 PM

Answers

  • Ok, it makes it clearer.

    So, 

    ;with cteLatestAttending as

    (select *, row_number() over

    (partition by PatientID order by VisitDate DESC)

    as Rn from PatientsInfo

    where CareType = 'Attending'), cteToTransfer as (select *

    from cteLatestAttending

    where Rn = 1 and DrId = @DoctorId) select T.*, P.* from cteToTransfer T

    OUTER APPLY (select top (1) DrId as PrimaryDrId,

    Doctor as PrimaryDoctor from PatientsInfo P where P.PatientID = T.PatientId

    and P.CareType = 'Primary Service Coordinator' ORDER BY VisitDate DESC) P


    I believe this will give you the result you're looking for. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles




    • Edited by Naomi N Thursday, December 19, 2013 6:55 PM
    • Marked as answer by DavidWIII Thursday, December 19, 2013 8:54 PM
    Thursday, December 19, 2013 5:04 PM
  • You need to give an alias to the derived table.

    WITH cteLatestAttending
    AS (
      SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY PatientID ORDER BY VisitDate DESC) AS Rn
      FROM
        PatientsInfo
      WHERE
        CareType = 'Attending'
     ),
    cteToTransfer
    AS (
      SELECT
        *
      FROM
        cteLatestAttending
      WHERE
        Rn = 1
        AND DrId = @DoctorId
     )
    SELECT
        T.*,
        P.*
    FROM
        cteToTransfer T
        OUTER APPLY
    (
    SELECT TOP (1)
    	DrId AS PrimaryDrId,
    	Doctor AS PrimaryDoctor
    FROM
    	PatientsInfo P
    WHERE
    	P.PatientID = T.PatientId
    	AND P.CareType = 'Primary Service Coordinator'
    ORDER BY
    	VisitDate DESC
    ) AS T -- <<<<<<<<<<<<<<<<<<<<<


    AMB

    Some guidelines for posting questions...


    • Edited by HunchbackMVP Thursday, December 19, 2013 6:56 PM
    • Marked as answer by DavidWIII Thursday, December 19, 2013 8:54 PM
    Thursday, December 19, 2013 6:56 PM

All replies

  • SELECT m1.DrID,
    m1.Doctor,
    m1.PatientID,
    m1.CareType,
    m2.DrID AS PrimaryID,
    m2.Doctor AS PrimaryDoctor
    FROM Mess m1
    INNER JOIN Mess m2
    ON m2.PatientID = m1.PatientID
    WHERE m1.DrID = 1372
    AND m1.CareType = 'Attending'
    AND m2.Caretype = 'Primary Service Coordinator'


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, December 19, 2013 4:38 PM
  • What if you have patients whose last visit is not Attending or Last visit attending but for a different doctor?

    The query above you used will select latest attending visit for that doctor, but it's not necessary the latest visit or latest status or latest doctor.

    So, can you clarify the desired output again assuming some patients last visit was with another doctor or the last status was not attending?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, December 19, 2013 4:40 PM
  • Try:

    WITH C1 AS (
    SELECT
    	DrID, 
    	Doctor, 
    	PatientID, 
    	CareType
    FROM
    	(
    	SELECT 
    		M.DrID, M.Doctor, M.PatientID, M.CareType
    		RANK() over (partition by M.PatientID order by M.VisitDate desc) as latest
    	FROM 
    		Mess  M
    	WHERE
    		M.DrID = '1372'
    		AND M.CareType = 'Attending'
    	) as T
    Where
    	latest = 1
    )
    , C2 AS (
    SELECT 
    	DrID, 
    	Doctor, 
    	PatientID
    FROM 
    	Mess  M
    WHERE
    	M.CareType = 'Primary Service Coordinator'
    )
    SELECT
    	C1.DrID, 
    	C1.Doctor, 
    	C1.PatientID, 
    	C1.CareType,
    	C2.DrID AS PrimaryID,
    	C2.Doctor AS PrimaryDoctor
    FROM
    	C1
    	LEFT OUTER JOIN
    	C2
    	ON C1.PatientID = C2.PatientID
    GO


    AMB

    Some guidelines for posting questions...

    • Proposed as answer by Praveen Rayan D'sa Thursday, December 19, 2013 4:49 PM
    • Unproposed as answer by Pan Zhang Tuesday, December 24, 2013 8:30 AM
    • Proposed as answer by Pan Zhang Tuesday, December 24, 2013 8:30 AM
    • Unproposed as answer by Naomi N Tuesday, December 24, 2013 3:57 PM
    Thursday, December 19, 2013 4:47 PM
  • I do need to clarify. I only need the patients that have a CareType = 'Attending", and the latest doctor with the CareType = 'Attending' would have DrID = 1372. If there are later visits with CareType equal to anything else that patient would still be selected. If there are earlier visits with CareType = 'Attending", they will be ignored if the latest is DrID = '1372'. If any patient has a DrID = '1372' with Caretype = 'Attending" with another DrID with CareType = 'Attending' at a later date, that patient would  not be selected.

    A doctor is leaving. The facility is assigning other 'Attending' doctors to his/her patients. If there is already a doctor with "Attending" as CareType, that patient has already been reassigned.

    I hope that makes it clear.


    • Edited by DavidWIII Thursday, December 19, 2013 4:52 PM typo
    Thursday, December 19, 2013 4:51 PM
  • I was considering using CTEs but didn't quite have it right. I believe Hunchback's reply is what I need. I'll have to post how it works later today.

    Thanks for all the replies.

    Thursday, December 19, 2013 4:58 PM
  • Ok, it makes it clearer.

    So, 

    ;with cteLatestAttending as

    (select *, row_number() over

    (partition by PatientID order by VisitDate DESC)

    as Rn from PatientsInfo

    where CareType = 'Attending'), cteToTransfer as (select *

    from cteLatestAttending

    where Rn = 1 and DrId = @DoctorId) select T.*, P.* from cteToTransfer T

    OUTER APPLY (select top (1) DrId as PrimaryDrId,

    Doctor as PrimaryDoctor from PatientsInfo P where P.PatientID = T.PatientId

    and P.CareType = 'Primary Service Coordinator' ORDER BY VisitDate DESC) P


    I believe this will give you the result you're looking for. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles




    • Edited by Naomi N Thursday, December 19, 2013 6:55 PM
    • Marked as answer by DavidWIII Thursday, December 19, 2013 8:54 PM
    Thursday, December 19, 2013 5:04 PM
  • My earlier explanation should say:

    A doctor is leaving. The facility is assigning other 'Attending' doctors to his/her patients. If there is already a doctor other than DrID 1372 with "Attending" as CareType with a later VisitDate, that patient has already been reassigned.

    That last sentence is supposed to mean that some patients who have an entry for DrID = 1372 and CareType = 'Attending' might already have a another doctor reassigned in the table. The VisitDate will be later for the doctor reassigned as 'Attending'. Those patients will not be selected.

    I'll try these solutions later and let you know.

    Thanks

    Thursday, December 19, 2013 5:27 PM
  • Yes, that's why my select statement first select latest Attending status and only those patients whose latest Attending status belong to that doctor.

    I used OUTER APPLY (subselect) to get the last primary doctor. There is no check in that select if that primary visit happened before or after that last attending visit. You can adjust the code if needed to compare dates. 

    Also, what if the patient was seen by his primary doctor after the attending visit with the leaving doctor?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, December 19, 2013 5:34 PM
  • quote: Also, what if the patient was seen by his primary doctor after the attending visit with the leaving doctor?

    Those patients would still be selected. It is confusing. I had the same question and should have included that. Those are different roles that a patient has assigned. The primary will probably be the doctor to assign a new attending doctor. That is why they are on the report. I did another report showing patients that do not have a primary. They are supposed to have both.

    Thursday, December 19, 2013 5:49 PM
  • Anyway, I think my query covers your problem. Did you have a chance to try it?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, December 19, 2013 5:52 PM
  • Naomi, I'm currently trying your solution.

    Right now I have a syntax error. According to the message it's near ')' on the last line:

    ORDER BY START_DATE DESC)

    That doesn't necessarily mean the error is on that line. It will take me some time to find the syntax error.

    Is the VisitDate in the Order by clause coming from P in the query? Is it the same as P.VisitDate?

    Thanks

    Thursday, December 19, 2013 6:44 PM
  • Yes, it's from the P so it should be the same as P.VisitDate.

    If you post the query you tried I can try to spot the error.

    I did a quick re-check and I see that the P alias is missing after the closing line. I've adjusted the code. There should be P right after the last )


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Thursday, December 19, 2013 6:56 PM
    Thursday, December 19, 2013 6:54 PM
  • You need to give an alias to the derived table.

    WITH cteLatestAttending
    AS (
      SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY PatientID ORDER BY VisitDate DESC) AS Rn
      FROM
        PatientsInfo
      WHERE
        CareType = 'Attending'
     ),
    cteToTransfer
    AS (
      SELECT
        *
      FROM
        cteLatestAttending
      WHERE
        Rn = 1
        AND DrId = @DoctorId
     )
    SELECT
        T.*,
        P.*
    FROM
        cteToTransfer T
        OUTER APPLY
    (
    SELECT TOP (1)
    	DrId AS PrimaryDrId,
    	Doctor AS PrimaryDoctor
    FROM
    	PatientsInfo P
    WHERE
    	P.PatientID = T.PatientId
    	AND P.CareType = 'Primary Service Coordinator'
    ORDER BY
    	VisitDate DESC
    ) AS T -- <<<<<<<<<<<<<<<<<<<<<


    AMB

    Some guidelines for posting questions...


    • Edited by HunchbackMVP Thursday, December 19, 2013 6:56 PM
    • Marked as answer by DavidWIII Thursday, December 19, 2013 8:54 PM
    Thursday, December 19, 2013 6:56 PM
  • Thanks Naomi and Hunchback. Giving it an alias did get rid of the syntax error. I'm verifying the data now. It might take some time.
    Thursday, December 19, 2013 7:13 PM
  • Naomi,

    Your query does work.

    I still haven't checked Hunchback's solution and don't know if I'll be able to today. I want to mark both as answers if his also works.

    Hunchback, Let me know if you think yours will still work after I posted clarifications.

    Thursday, December 19, 2013 8:44 PM
  • His will work if you move the doctor condition into the outer query (the same way I did).

    He used your original logic and I pointed out the possible problem with it. You need to get the latest attending status patients and then subselect the ones that have the doctor you need. Otherwise (the way your original query is written) you're getting the latest attending status patients that were seen by that doctor, but in theory there may be later rows with the attending status seen by the different doctor.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, December 19, 2013 8:49 PM
  • Yes, I see now. It turns out that I might have to use Hunchback's first answer for another report with maybe a slight variation.

    I'm glad I'm allowed to select two answers.

    Thanks, everyone.

    I just realized I forgot to give the data types, but it didn't really matter in this case.

    Thursday, December 19, 2013 8:58 PM
  • The other difference between my outer apply and his LEFT JOIN is that if you have several different Primary Care doctors/visits, they all will be selected by LEFT JOIN, but using top(1) in OUTER APPLY will select just one latest visit.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, December 19, 2013 9:07 PM