place_of_service_source_value

SUS Outpatient Care Site

• Value copied from MainSpecialtyCode

• MainSpecialtyCode A unique code identifying each MAIN SPECIALTY designated by Royal Colleges. MAIN SPECIALTY CODE

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 MainSpecialtyCode

• MainSpecialtyCode A unique code identifying each MAIN SPECIALTY designated by Royal Colleges. MAIN SPECIALTY CODE

	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.