MEDICATION_STATEMENT: many records have null concept_id values

[BillWoodNHS]

The script below produces a significant volume of medication_statement records with a null concept ID value for:

• AuthorisationType concept_id
• Core_concept_ID

SELECT COUNT([LakehouseDateTimeLoaded])
FROM [CDM_masked].[vMedicationStatement]
WHERE [LDSConceptId_AuthorisationType] IS NULL 

SELECT COUNT([LakehouseDateTimeLoaded])
FROM [CDM_masked].[vMedicationStatement]
WHERE [LDSConceptId_DatePrecision] IS NULL 

SELECT COUNT([LakehouseDateTimeLoaded])
FROM [CDM_masked].[vMedicationStatement]
WHERE [LDSConceptId_Core] IS NULL 

SELECT COUNT([LakehouseDateTimeLoaded])
FROM [CDM_masked].[vMedicationStatement]

[EddieDavison92]

All rows are no longer null, but >99% of rows are still null for the two core fields.

Script:

SELECT 
    'Total Rows' AS metric,
    COUNT(*) AS count,
    100.0 AS percentage
FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.MEDICATION_STATEMENT

UNION ALL

SELECT 
    'medication_statement_core_concept_id - Populated' AS metric,
    COUNT(*) AS count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.MEDICATION_STATEMENT), 2) AS percentage
FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.MEDICATION_STATEMENT
WHERE "medication_statement_core_concept_id" IS NOT NULL

UNION ALL

SELECT 
    'medication_statement_core_concept_id - Missing' AS metric,
    COUNT(*) AS count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.MEDICATION_STATEMENT), 2) AS percentage
FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.MEDICATION_STATEMENT
WHERE "medication_statement_core_concept_id" IS NULL

UNION ALL

SELECT 
    'authorisation_type_concept_id - Populated' AS metric,
    COUNT(*) AS count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.MEDICATION_STATEMENT), 2) AS percentage
FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.MEDICATION_STATEMENT
WHERE "authorisation_type_concept_id" IS NOT NULL

UNION ALL

SELECT 
    'authorisation_type_concept_id - Missing' AS metric,
    COUNT(*) AS count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.MEDICATION_STATEMENT), 2) AS percentage
FROM "Data_Store_OLIDS_UAT".OLIDS_MASKED.MEDICATION_STATEMENT
WHERE "authorisation_type_concept_id" IS NULL

ORDER BY 
    CASE 
        WHEN metric = 'Total Rows' THEN 1
        WHEN metric LIKE 'medication_statement_core_concept_id%' THEN 2
        WHEN metric LIKE 'authorisation_type_concept_id%' THEN 3
    END,
    CASE 
        WHEN metric LIKE '% - Populated' THEN 1
        WHEN metric LIKE '% - Missing' THEN 2
    END;

[answerplant]

Resolved inn ew release.