0
92
ViewsUnique records quarter over quarter
I have a table like so -
patient_id date org
I5434 2020-06-05 Methodist Hospital
ertw4 2020-08-03 New York Hospital
ouiku35 2020-08-30 Tristar
ouiku35 2020-01-05 Tristar
ouiku35 2020-04-22 Tristar
The output I am needing is
quarter count
2020Q1 1
2020Q2 1
2020Q3 1
3rd record in the table above is not counted because it is the same patient in prior quarters at the same hospital
·
Santiago Trujillo
1 answers
Answer question0
We can try using a calendar table approach here:
WITH quarters AS (
SELECT 2020 AS year, 1 AS quarter, '2020Q1' AS label UNION ALL
SELECT 2020, 2, '2020Q2' UNION ALL
SELECT 2020, 3, '2020Q3' UNION ALL
SELECT 2020, 4, '2020Q4'
)
SELECT q.label, COUNT(t.date) AS count
FROM quarters q
LEFT JOIN yourTable t
ON EXTRACT(year FROM t.date) = q.year AND
EXTRACT(quarter FROM t.date) = q.quarter
GROUP BY q.label
ORDER BY q.label;
·
Santiago Trujillo
Report
Answer question