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
order by 
	SiteCodeofTreatment, 
	MainSpecialtyCode 
	

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
order by 
	SiteCodeOfTreatmentAtEpisodeStartDate, 
	MainSpecialtyCode 
	

Comment or raise an issue for this mapping.