March 3, 2023 at 1:32 am
Hello - I would like to show a record for a patients last visit regardless of what doctor they saw. If patient saw a doctor who is not their primary doctor on their last visit by date, show the record and then show the record of the last time they saw their primary doctor. If the last visit by date was with the primary doctor, then only show that record. Apologies in advance as I am trying to be clear as possible and I am stuck. Thanks
Sample data:
primarydoc, client_id, visitdoc, doc_id, visit_id, visit_date
Mary, 1000221, Clara, 4100, 12125, 2022-12-25
Mary, 1000221, Clara, 4100, 54114, 2022-11-24
Mary, 1000221, Mary, 4520, 44114, 2022-08-21
Sue, 1000745, Sue, 2144, 57337, 2022-12-16
Sue, 1000745, Sue, 2144, 57717, 2022-12-13
Sue, 1000745, Betty, 2198, 54587, 2022-11-22
Karen, 1007421, Molly, 7110, 92125, 2022-12-29
Karen, 1007421, Karen, 5100, 64214, 2022-11-28
Karen, 1007421, Molly, 7110, 88714, 2022-09-23
So far my code just gets the last visit date of the patient, but I need the following results: Results should be only the blue records
SELECT t.*, l.client_id as client_id_2, latest_visitdate
FROM testvisit t
JOIN (
SELECT client_id,
MAX(visit_date) latest_visitdate
FROM testvisit
GROUP BY client_id ) l
ON t.client_id = l.client_id
AND t.visit_date = l.latest_visitdate
ORDER BY t.client_id
Mary, 1000221, Clara, 4100, 12125, 2022-12-25 Last patient visit is required and Clara is NOT the primary doctor, Clara staff doctor
Mary, 1000221, Clara, 4100, 54114, 2022-11-24
Mary, 1000221, Mary, 4520, 44114, 2022-08-21 Record is required because Mary the primary doctor did not see patient last most recent visit (2022-12-25) , so we must show last visit with the primary doctor
Sue, 1000745, Sue, 2144, 57337, 2022-12-16 Last patient visit is required and Sue the primary doctor was part of last visit - done
Sue, 1000745, Sue, 2144, 57717, 2022-12-13
Sue, 1000745, Betty, 2198, 54587, 2022-11-22
Karen, 1007421, Molly, 7110, 92125, 2022-12-29 Last patient visit is required and Molly is NOT the primary doctor, Molly staff doctor
Karen, 1007421, Karen, 5100, 64214, 2022-11-28 Record is required because Karen the primary doctor did not see patient last most recent visit, so we must show last visit with the primary doctor
Karen, 1007421, Molly, 7110, 88714, 2022-09-23
March 3, 2023 at 2:10 am
CREATE TABLE testvisit (
primarydoc varchar(50),
client_id int,
visitdoc varchar(50),
doc_id int,
visit_id int,
visit_date date
);
INSERT INTO testvisit (primarydoc, client_id, visitdoc, doc_id, visit_id, visit_date)
VALUES
('Mary', 1000221, 'Clara', 4100, 12125, '2022-12-25'),
('Mary', 1000221, 'Clara', 4100, 54114, '2022-11-24'),
('Mary', 1000221, 'Mary', 4520, 44114, '2022-08-21'),
('Sue', 1000745, 'Sue', 2144, 57337, '2022-12-16'),
('Sue', 1000745, 'Sue', 2144, 57717, '2022-12-13'),
('Sue', 1000745, 'Betty', 2198, 54587, '2022-11-22'),
('Karen', 1007421, 'Molly', 7110, 92125, '2022-12-29'),
('Karen', 1007421, 'Karen', 5100, 64214, '2022-11-28'),
('Karen', 1007421, 'Molly', 7110, 88714, '2022-09-23')
go
WITH latest_visits AS (
SELECT
client_id,
MAX(visit_date) AS latest_visit_date
FROM testvisit
GROUP BY client_id
),
latest_primary_visits AS (
SELECT
t.client_id,
t.visitdoc,
MAX(t.visit_date) AS latest_primary_visit_date
FROM testvisit t
WHERE t.visitdoc = t.primarydoc
GROUP BY t.client_id, t.visitdoc
)
SELECT
t.*,
CASE
WHEN t.visit_date = l.latest_visit_date AND t.visitdoc <> t.primarydoc THEN 'Last visit seen by staff doctor'
WHEN t.visit_date = l.latest_visit_date AND t.visitdoc = t.primarydoc THEN 'Last visit seen by primary doctor'
WHEN t.visit_date <> l.latest_visit_date AND t.visitdoc = t.primarydoc THEN 'Last visit seen by primary doctor (previous visit)'
END AS seen_by_primary
FROM testvisit t
JOIN latest_visits l ON t.client_id = l.client_id
LEFT JOIN latest_primary_visits lpv ON t.client_id = lpv.client_id
AND t.visitdoc = lpv.visitdoc
AND t.visit_date = lpv.latest_primary_visit_date
WHERE t.visit_date = l.latest_visit_date
OR lpv.latest_primary_visit_date IS NOT NULL
ORDER BY t.client_id, t.visit_date DESC;
March 3, 2023 at 2:27 pm
;WITH cte_last_visits AS (
SELECT client_id,
MAX(visit_date) AS last_visit,
MAX(CASE WHEN primarydoc = visitdoc THEN visit_date END) AS last_primarydoc_visit
FROM testvisit
GROUP BY client_id
)
SELECT tv.*
FROM cte_last_visits clv
INNER JOIN testvisit tv ON (tv.client_id = clv.client_id AND tv.visit_date = clv.last_visit) OR
(clv.last_visit <> clv.last_primarydoc_visit AND tv.client_id = clv.client_id AND
tv.visit_date = clv.last_primarydoc_visit)
ORDER BY client_id, visit_date DESC
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 3, 2023 at 11:21 pm
Thank you so much. This works great. Much appreciated
March 4, 2023 at 2:18 am
Another way...
SELECT *
FROM (
SELECT a.*
,(CASE
WHEN (a.primarydoc = b.visitdoc AND
b.patient_last_visit_primarydoc = a.visit_date)
THEN 1
ELSE CASE
WHEN a.primarydoc <> b.visitdoc AND
a.visit_date = b.patient_last_visit_date
THEN 1
ELSE 0
END
END) AS qualified_rows
FROM testvisit a
INNER JOIN (SELECT
*
,MAX(t.visit_date) OVER (PARTITION BY t.client_id ORDER BY t.visit_date DESC) patient_last_visit_date
,MAX(CASE
WHEN t.primarydoc = t.visitdoc
THEN t.visit_date
END)
OVER (PARTITION BY t.client_id, t.visitdoc ORDER BY t.visit_date DESC) patient_last_visit_primarydoc
FROM testvisit t) b
ON a.client_id = b.client_id
AND a.visit_date = b.visit_date)patientvisits
WHERE patientvisits.qualified_rows=1
=======================================================================
March 6, 2023 at 12:31 am
Thank you Emperor100
March 6, 2023 at 5:45 pm
I believe that the following solution will perform better.
WITH last_visits AS
(
SELECT t.primarydoc
, t.client_id
, t.visitdoc
, t.doc_id
, t.visit_id
, t.visit_date
, ROW_NUMBER() OVER(PARTITION BY t.client_id ORDER BY t.visit_date DESC) AS rn
, ROW_NUMBER() OVER(PARTITION BY t.client_id ORDER BY CASE WHEN t.primarydoc = t.visitdoc THEN 1 ELSE 2 END, t.visit_date DESC) AS rn2
FROM testvisit AS t
)
SELECT *
FROM last_visits AS lv
WHERE lv.rn = 1
OR lv.rn2 = 1;
Here are the read counts for the various solutions.
Jonathan AC Roberts
Table '#testvisit'. Scan count 19, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 3, logical reads 49, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Scott Pletcher
Table 'Worktable'. Scan count 3, logical reads 49, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#testvisit'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Emperor100
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 27, logical reads 182, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#testvisit. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Drew Allen
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#testvisit'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply