March 2, 2018 at 9:23 am
My dataset looks like this:
Provider Patient Visits
Dr. A John Smith 1
Dr. B John Smith 2
Dr. C John Smith 1
Dr. D John Smith 1
Dr. E Suzy Q 5
Dr. B Suzy Q 3
Dr. A Suzy Q 4
I want to return a single distinct record for each patient which will be the record with the highest visit count. How would I do this?
For my example the return set would be:
Provider Patient Visits
Dr. B John Smith 2
Dr. E Suzy Q 5
March 2, 2018 at 9:42 am
I figured it out. I came up with the following:
RANKING AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY d.Patient ORDER BY d.visits DESC) AS row_num,
d.Provider,
d.Patient,
d.visits
FROM DETAILS d
)
SELECT
*
FROM RANKING r
WHERE r.row_num = 1
ORDER BY r.Provider;
March 2, 2018 at 10:08 am
And how do you handle a tie? Where the patient sees multiple providers the same number of max times.
March 2, 2018 at 10:12 am
Good question, I was just thinking about that too. I am going to bring in the last encounter date and choose the provider they most recently seen.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply