specialty_source_value

SUS Inpatient Provider

• Value copied from MainSpecialtyCode

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

with counts as (
	select 
	ConsultantCode,
	MainSpecialtyCode,
	count(*) as SpecialtyFrequency,
	row_number() over (
	partition by ConsultantCode 
	order by count(*) desc, MainSpecialtyCode
) rnk
from (
	select
	ConsultantCode,
	MainSpecialtyCode
	from [OMOP_QA].[omop_staging].[sus_APC]
	where MainSpecialtyCode is not null
	and ConsultantCode is not null
) grouped
	group by ConsultantCode, MainSpecialtyCode
)
select 
	ConsultantCode,
	MainSpecialtyCode
	from counts
	where rnk = 1;
	

Comment or raise an issue for this mapping.