Trying to count unique observations in SQL using Partition By

Question

I have these two datasets:

Conditions: I would like to count the number of Unique Discharge_ID as Total_Discharges in my final dataset.

ICU_ID is a little bit more difficult. For PT_ID 001, what is happening is that PT 001 has 4 of the same discharge dates but 4 unique ICU_IDs. Since all of these ICU_IDs occur within 30 days of the Discharge_DT, I only want to count one of them. That is why total discharges for AZ is 1 and ICU_Admits = 1.

For PT_ID 002, I have 2 different Discharge_IDs but 1 ICU Admit that occurred within 30 days of both of the Discharge_IDs. I would like to count the Discharges as 2, and ICU_admits as 1.

DF1: Dataset of Discharges from hospital and admission to ICU within 30 days of Discharge_DT

City	PT_ID	Hospital_ID	Admit_Dt	Discharge_DT	Discharge_ID	ICU_ID
AZ	001	ABC	01-01-2021	01-03-2021	001,ABC,01-01-2021,01-03-2021	001,XYZ,01-05-2021,01-06-2021
AZ	001	ABC	01-01-2021	01-03-2021	001,ABC,01-01-2021,01-03-2021	001,XYZ,01-08-2021,01-09-2021
AZ	001	ABC	01-01-2021	01-03-2021	001,ABC,01-01-2021,01-03-2021	001,XYZ,01-11-2021,01-11-2021
AZ	001	ABC	01-01-2021	01-03-2021	001,ABC,01-01-2021,01-03-2021	001,XYZ,01-15-2021,01-16-2021
CA	002	DEF	04-03-2021	04-07-2021	001,ABC,04-03-2021,04-07-2021	002,LMN,04-27-2021,04-27-2021
CA	002	DEF	04-20-2021	04-21-2021	001,ABC,04-20-2021,04-21-2021	002,LMN,04-27-2021,04-27-2021

DF desired:

City	TotalDischarges	ICU_Admit
AZ	1	1
CA	2	1

Current Code:

DROP TABLE IF EXISTS #edit1

WITH CTE_df1 as (
select * from df1
)
select 
        City,
        PT_ID,
        Hospital_ID,
        Admit_Dt,
        Discharge_DT,
        Discharge_ID,
        count(ICU_ID) over (partition by ICU_ID) as ICU_Pts,
        count(distinct Discharge_ID) as Total_Discharges
into #edit1
from CTE_df1
group by City, Discharge_ID, ICU_ID, PT_ID
order by City,



;with CTE_edit1 as (
select * from #edit1
)
select City, sum(ICU_Pts), sum(Total_Discharges)
from CTE_edit1
group by City
order by City

Current Output: PT_ID 001 works great but PT_ID 002 shows up at 2 in ICU_Admit as it is counting both as unique ICU visits.

City	TotalDischarges	ICU_Admit
AZ	1	1
CA	2	2

Any help would be appreciated

select distinct * from df1, then do select count(...) group by City over that? — Conor Cunningham MSFT, Sep 27 '22 at 19:40
This was exactly it. I was missing the distinct inside the CTE. Thank you so much! — Trevor M, Sep 27 '22 at 20:12

Trying to count unique observations in SQL using Partition By

0 Answers0