care_site_name

SUS Outpatient Care Site

• Value copied from SiteCodeofTreatment

• SiteCodeofTreatment ORGANISATION SITE CODE of the ORGANISATION where the PATIENT was treated SITE CODE (OF TREATMENT)

with 
counts as ( 
	select 
	SiteCodeofTreatment, 
	MainSpecialtyCode, 
	count(*) as cnt 
	from omop_staging.sus_OP 
	where SiteCodeofTreatment is not null 
	and MainSpecialtyCode is not null 
	group by 
	SiteCodeofTreatment, 
	MainSpecialtyCode 
), 

ranked as ( 
	select 
	SiteCodeofTreatment, 
	MainSpecialtyCode, 
	cnt, 
	row_number() over ( 
	partition by SiteCodeofTreatment 
	order by cnt desc, MainSpecialtyCode 
	) as rnk 
	from counts 
)
select 
	SiteCodeofTreatment, 
	MainSpecialtyCode 
from ranked 
where rnk = 1;
	

Comment or raise an issue for this mapping.

SUS Inpatient Care Site

• Value copied from SiteCodeOfTreatmentAtEpisodeStartDate

• SiteCodeOfTreatmentAtEpisodeStartDate ORGANISATION SITE CODE of the ORGANISATION where the PATIENT was treated SITE CODE (OF TREATMENT)

	with 
	counts as ( 
		select 
		SiteCodeOfTreatmentAtEpisodeStartDate, 
		MainSpecialtyCode, 
		count(*) as cnt 
		from omop_staging.sus_APC 
		where SiteCodeOfTreatmentAtEpisodeStartDate is not null 
		and MainSpecialtyCode is not null 
		group by 
		SiteCodeOfTreatmentAtEpisodeStartDate, 
		MainSpecialtyCode 
	), 

	ranked as ( 
		select 
		SiteCodeOfTreatmentAtEpisodeStartDate, 
		MainSpecialtyCode, 
		cnt, 
		row_number() over ( 
		partition by SiteCodeOfTreatmentAtEpisodeStartDate 
		order by cnt desc, MainSpecialtyCode 
		) as rnk 
		from counts 
	)
	select 
		SiteCodeOfTreatmentAtEpisodeStartDate, 
		MainSpecialtyCode 
	from ranked 
	where rnk = 1;
	

Comment or raise an issue for this mapping.

SUS Inpatient Care Site

• Value copied from SiteCodeOfTreatment

• SiteCodeOfTreatment ORGANISATION SITE CODE of the ORGANISATION where the PATIENT was treated SITE CODE (OF TREATMENT)

	select distinct
		SiteCodeOfTreatment
	from [omop_staging].[sus_AE] 	
	

Comment or raise an issue for this mapping.