Reputation: 721
Could someone please help break up this complex SQL query into individual steps? I am trying to build multiple tables and joins out of this multistep query that feels quite opaque. Thank you!
select
visit_date,
count(vo1.visit_id) as num_visits,
sum(case when co1.person_id is not null then 1 else 0 end) as num_visits_w_cond
from
visit_occurrence vo1
left join
(select distinct
person_id, visit_id, condition_date
from
condition_occurrence
where
condition_id = 12345)) co1 on vo1.person_id = co1.person_id
and vo1.visit_date = co1.condition_date
and vo1.visit_id = co1.visit_id
where
visit_id = 1234
group by
visit_date
order by
visit_date;
Ideally, I'd like to generate a few data tables in the intermediate steps and then join and count at the end but am not sure what this would look like.
EDIT: Thank you for your comments. Regarding clarity of the individual tables:
The first select will query the visit_occurrence table for all visit_ids that match a visit_id # of "1234" and return the distinct person_id, visit_date, and visit_id for all visits. So a person_id, visit_date, visit_id tuple is unique, i.e. the same person_id with a different visit_date or different visit_id does not qualify as a duplicate. Only an identical tuple is a duplicate.
The second select will query the condition_occurrence table for all condition_ids that match a condition_id # of 12345 and return distinct person_id, visit_id, condition_date. So a person_id, visit_id, condition_date tuple is unique, i.e. the same person_id with a different visit_id or condition_date is not a duplicate. Only an identical tuple is a duplicate.
Join table 1 and table 2 on person_id, visit_date = condition_date, visit_id = visit_id. Then count how many distinct person_ids occur on each date.
From table 1, count how many visit_ids are associated with each date.
Hopefully that's more clear? Thank you again for the feedback.
Upvotes: 0
Views: 1424
Reputation: 395
I doubt this will perform any better, but it's close to what you're asking for. I would stay away from temp tables (which is what I inferred from your question).
with cteVO as --common table expression for visit_occurrence
(
select distinct person_id, visit_date, visit_id
from visit_occurrence
where visit_id = 1234
),
cteCO as -- common table expression for condition_occurrence
(
select distinct person_id, visit_id, condition_date
from condition_occurrence
where condition_id = 12345
)
-- Join both CTEs to get the count of person_id and count of visit_id
SELECT cteVO.visit_date, COUNT(cteVO.person_id) AS count_person_id,
COUNT(cteVO.visit_id) AS count_visit_id
FROM cteVO
INNER JOIN cteCO ON cteVO.person_id = cteCO.person_id
AND cteVO.visit_date = cteCO.condition_date
AND cteVO.visit_id = cteCO.visit_id
GROUP BY cteVO.visit_date
Upvotes: 1