March 27, 2014 at 12:22 pm
I'm trying to figure out how to find the oldest two distinct records per Patient by Distinct VisitType. I have been going through all of the Top/Max/Distinct posts but nothing seems to fit my scenario.
Here is the background, a Patient walks into clinic and is assigned (PatientID) this never changes for them. They are then seen for services. Each visit has its own (VisitID) and each visit will also have a VisitType associated with it. (Medical, Dental, Therapy, ect). Each visit also has a VisitDate.
What I'm trying to query is what was the first two Distinct VisitTypes a Patient has ever had for all patients in our records.
Example of desired result:
PatientID VisitID VisitType VisitDate
32333 98765 Dental 1/1/2012
32333 98798 Medical 10/10/2013
34555 99345 Therapy 8/5/2013
34555 99567 Dental 10/28/2013
So far all of the stuff I have tried only gets me partly there.
This is a generic sample table of the one I'm actually working on. If you would like me to post all of my goofy tries at this please let me know. I'm stuck! I should have never took a break to start learning XAML and C#
CREATE TABLE visit_info_table
(
PatientID numeric(18,0) not null,
VisitID numeric (18,0) not null,
VisitType varchar (32),
VisitDate datetime )
INSERT INTO visit_info_table (PatientID, VisitID, VisitType, VisitDate)
VALUES (24356, 765889, 'Medical', 07/02/12), (24356, 765469, 'Dental', 09/02/13),
(34567, 876576, 'Therapy', 06/03/12), (34567, 987657, 'Medical', 02/01/13),
(32335, 986543, 'Dental', 09/12/13), (33456, 986577, 'Therapy', 01/04/14),
(32335, 986553, 'Medical', 10/11/13), (33456, 986566, 'Dental', 01/01/12),
(32335, 876546, 'Therapy', 11/23/13), (33456, 897654, 'Medical', 02/03/14),
(34567, 765834, 'Medical', 03/03/13), (32455, 987686, 'Dental', 06/03/12),
(33765, 876578, 'Therapy', 12/23/13), (32455, 876965, 'Therapy', 07/12/13),
(33765, 876854, 'Medical', 11/13/12), (32455, 765489, 'Dental', 05/14/12),
(34567, 986754, 'Therapy', 11/23/13), (34567, 876546, 'Dental', 06/14/12)
***SQL born on date Spring 2013:-)
March 27, 2014 at 12:44 pm
Maybe something like this:
WITH FirstVisits AS(
SELECT PatientID,
VisitID,
VisitType,
VisitDate,
ROW_NUMBER() OVER( PARTITION BY PatientID, VisitType ORDER BY VisitDate) firstvisit
FROM #visit_info_table
),
RowsCTE AS(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY PatientID ORDER BY VisitDate) rn
FROM FirstVisits
WHERE firstvisit = 1
)
SELECT PatientID, VisitID, VisitType, VisitDate
FROM RowsCTE
WHERE rn <= 2
Note that your sample data doesn't match your expected results.
March 27, 2014 at 12:58 pm
Perhaps something like this?; WITH VisitTypes AS (
SELECT PatientID, VisitType, ROW_NUMBER() OVER ( PARTITION BY PatientID, VisitType ORDER BY VisitDate) AS Cnt, VisitDate
FROM #visit_info_table
) SELECT TOP 2 PatientID, VisitType, VisitDate FROM VisitTypes
WHERE PatientID = 32333
AND Cnt = 1
ORDER BY VisitDate
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 27, 2014 at 1:03 pm
MyDoggieJessie (3/27/2014)
Perhaps something like this?; WITH VisitTypes AS (SELECT PatientID, VisitType, ROW_NUMBER() OVER ( PARTITION BY PatientID, VisitType ORDER BY VisitDate) AS Cnt, VisitDate
FROM #visit_info_table
) SELECT TOP 2 PatientID, VisitType, VisitDate FROM VisitTypes
WHERE PatientID = 32333
AND Cnt = 1
ORDER BY VisitDate
That would only give the result for one patient and not all the patients.
March 27, 2014 at 1:11 pm
Of course, I was just matching the required output that the OP requested. This would need to be removed for a full result set. I liked your approach, you beat me to the reply 🙂
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 27, 2014 at 1:37 pm
Thanks guys! Your fine gentlemen :cool
Luis yours seems to work for me, thank you very much. My first whack at it was using a CTE, I then tried a half a dozen other ways but always seemed to end up just getting the one patient and not all of them.
Thanks so much!:-D
***SQL born on date Spring 2013:-)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply