procedure_concept_id

SUS Outpatient Procedure Occurrence

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

SUS CCMDS Procedure Occurrence

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

SUS APC Procedure Occurrence

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

SUS AE Procedure Occurrence

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

Rtds Procedure Occurrence

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

Oxford Procedure Occurrence

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

CosdV9LungProcedureOccurrenceRelapseMethodOfDetection

Source column RelapseMethodOfDetection. Lookup RelapseMethodOfDetection concepts for lung cancer recurrence.

RelapseMethodOfDetection	procedure_concept_id	notes
1	4303062	Morphology
2	4276031	Flow
3	4233623	Molecular
4	4240345	Clinical Examination
9	0	Other (not listed)

Notes

OMOP Morphology
OMOP Flow
OMOP Molecular
OMOP Clinical Examination
RELAPSE - METHOD OF DETECTION
RelapseMethodOfDetection A code representing the method used to detect a relapse or recurrence. RELAPSE - METHOD OF DETECTION

with LU as (
    select
        Record ->> '$.PrimaryPathway.ReferralAndFirstStageOfPatientPathway.DateFirstSeen' as DateFirstSeen,
        Record ->> '$.PrimaryPathway.ReferralAndFirstStageOfPatientPathway.DateFirstSeenCancerSpecialist' as DateFirstSeenCancerSpecialist,
        Record ->> '$.PrimaryPathway.LinkageDiagnosticDetails.DateOfPrimaryDiagnosisClinicallyAgreed' as DateOfPrimaryDiagnosisClinicallyAgreed,
        Record ->> '$.PrimaryPathway.Staging.StageDateFinalPretreatmentStage' as StageDateFinalPretreatmentStage,
        Record ->> '$.PrimaryPathway.Staging.StageDateIntegratedStage' as StageDateIntegratedStage,
        Record ->> '$.Treatment.TreatmentStartDateCancer' as TreatmentStartDateCancer,
        Record ->> '$.Treatment.Surgery.ProcedureDate' as ProcedureDate,
        unnest ([[Record ->> '$.NonPrimaryPathway.Recurrence.Relapse-MethodOfDetection.@code'], Record ->> '$.NonPrimaryPathway.Recurrence.Relapse-MethodOfDetection[*].@code'], recursive := true) as RelapseMethodOfDetection,
        Record ->> '$.LinkagePatientId.NhsNumber.@extension' as NhsNumber
    from omop_staging.cosd_staging_901
    where type = 'LU'
)
select
    distinct
        RelapseMethodOfDetection,
        NhsNumber,
        least(
            cast(DateFirstSeen as date),
            cast(DateFirstSeenCancerSpecialist as date),
            cast(DateOfPrimaryDiagnosisClinicallyAgreed as date),
            cast(StageDateFinalPretreatmentStage as date),
            cast(StageDateIntegratedStage as date),
            cast(TreatmentStartDateCancer as date),
            cast(ProcedureDate as date)
        ) as Date
from LU o
where o.RelapseMethodOfDetection is not null
  and not (
        DateFirstSeen is null and
        DateFirstSeenCancerSpecialist is null and
        DateOfPrimaryDiagnosisClinicallyAgreed is null and
        StageDateFinalPretreatmentStage is null and
        StageDateIntegratedStage is null and
        TreatmentStartDateCancer is null and
        ProcedureDate is null
    )

Comment or raise an issue for this mapping.

Cosd V9 Lung Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

Cosd V9 Lung Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

CosdV8LungProcedureOccurrenceRelapseMethodOfDetection

Source column RelapseMethodDetectionType. Lookup RelapseMethodOfDetection concepts for lung cancer recurrence.

RelapseMethodDetectionType	procedure_concept_id	notes
1	4303062	Morphology
2	4276031	Flow
3	4233623	Molecular
4	4240345	Clinical Examination
9	0	Other (not listed)

Notes

OMOP Morphology
OMOP Flow
OMOP Molecular
OMOP Clinical Examination
RELAPSE - METHOD OF DETECTION
RelapseMethodDetectionType A code representing the method used to detect a relapse or recurrence. RELAPSE - METHOD OF DETECTION

with LU as (
    select 
        Record ->> '$.Lung.LungCore.LungCoreReferralAndFirstStageOfPatientPathway.DateFirstSeen' as DateFirstSeen,
        Record ->> '$.Lung.LungCore.LungCoreReferralAndFirstStageOfPatientPathway.SpecialistDateFirstSeen' as SpecialistDateFirstSeen,
        Record ->> '$.Lung.LungCore.LungCoreLinkageDiagnosticDetails.ClinicalDateCancerDiagnosis' as ClinicalDateCancerDiagnosis,
        Record ->> '$.Lung.LungCore.LungCoreStaging.IntegratedStageTNMStageGroupingDate' as IntegratedStageTNMStageGroupingDate,
        Record ->> '$.Lung.LungCore.LungCoreStaging.FinalPreTreatmentTNMStageGroupingDate' as FinalPreTreatmentTNMStageGroupingDate,
        unnest ([[Record ->> '$.Lung.LungCore.LungCoreTreatment.CancerTreatmentStartDate'], Record ->> '$.Lung.LungCore.LungCoreTreatment[*].CancerTreatmentStartDate'], recursive := true) as CancerTreatmentStartDate,
        Record ->> '$.Lung.LungCore.LungCoreTreatment.LungCoreSurgeryAndOtherProcedures.ProcedureDate' as ProcedureDate,
        Record ->> '$.Lung.LungCore.LungCoreNonPrimaryCancerPathwayALLAMLAndMPAL.RelapseMethodDetectionType.@code' as RelapseMethodDetectionType,
        Record ->> '$.Lung.LungCore.LungCoreLinkagePatientId.NHSNumber.@extension' as NhsNumber
    from omop_staging.cosd_staging_81
    where Type = 'LU'
)
select
      distinct
          RelapseMethodDetectionType,
          NhsNumber,
          least(
                cast (DateFirstSeen as date),
                cast (SpecialistDateFirstSeen as date),
                cast (ClinicalDateCancerDiagnosis as date),
                cast (IntegratedStageTNMStageGroupingDate as date),
                cast (FinalPreTreatmentTNMStageGroupingDate as date),
                cast (CancerTreatmentStartDate as date),
                cast (ProcedureDate as date)
              ) as Date
from LU o
where o.RelapseMethodDetectionType is not null
  and not (
    DateFirstSeen is null and
    SpecialistDateFirstSeen is null and
    ClinicalDateCancerDiagnosis is null and
    IntegratedStageTNMStageGroupingDate is null and
    FinalPreTreatmentTNMStageGroupingDate is null and
    CancerTreatmentStartDate is null and
    ProcedureDate is null
    )

Comment or raise an issue for this mapping.

Cosd V8 Lung Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 Lung Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

Cosd V9 Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

Cosd V9 Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

Cosd V8 Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

Cosd V8 Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 Breast Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 Breast Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 Breast Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 Breast Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.