Orphaned Records: - 186 Observations do not have a patient_id - 24 Medication orders do not have a core_concept_id

[Rkw20067]

Orphaned Records:

• 186 Observations do not have a patient_id
• 24 Medication orders do not have a core_concept_id
  This is low priority.

[BillWoodNHS]

confirmed 186 observation records in CDM observation table have null patient id value:

however also confirmed that there are zero EMIS source records with a null patientGUID:

This indicates that there is an issue with the transformation from EMIS to the CDM.
the full logic is at the end of this comment, however please note the specific section below:

LEFT JOIN [Sequenced_masked].[vPrimaryCareEMIS_Admin_Patient] P1
		ON CO1.[PatientGuid] = P1.[PatientGuid]
			AND P1.Deleted = 0

Note

This shows that the join to extract the id for the patient is filtered to only active patients at the time of the join.

This will limit the return to just active patients. This will need to be amended to choose the top-ranked / latest version of the patient record, rather that only active patient records.

full transform logic below:

CREATE VIEW [Transform_masked].[PrimaryCareEMIS_CareRecord_Observation_Observation] AS WITH Concept_Systems
AS (
	SELECT [System]
		,[ColumnName]
	FROM [CDM_reference].[FieldCodeSystem] FCS1
	WHERE FCS1.[ObjectName] = 'CareRecord_Observation'
		AND FCS1.[LDSDataSetId] = 1
	)
SELECT [LDSBusinessId]
	,[LDSBusinessKey]
	,[LDSRecordId]
	,[LDSRecordHash] = CAST(HASHBYTES('SHA2_256', CONCAT_WS('#', [ExtractStartTime], [InitialExtractStartTime], [RecordOwnerOrganisationCode], [LDSBusinessId_Patient], [LDSBusinessId_Encounter], [LDSBusinessId_Practitioner], [LDSBusinessId_ParentObservation], [ClinicalEffectiveDate], [LDSConceptId_DatePrecision], [ResultValue], [LDSConceptId_ResultUnits], [ResultDate], [ResultText], [IsProblem], [IsReview], [ProblemEndDate], [LDSConceptId_Source], [AgeAtEvent], [AgeAtEventBaby], [AgeAtEventNeonate], [LDSConceptId_Episodicity], [IsPrimary], [DateRecorded], [IsDeleted], [IsConfidential])) AS BINARY (32))
	,[LDSDataSetId]
	,[RawStaged_EventId]
	,[ExtractStartTime]
	,[InitialExtractStartTime]
	,[LDSIsDeleted]
	,[RecordOwnerOrganisationCode]
	,[LDSBusinessId_Patient]
	,[LDSBusinessId_Encounter]
	,[LDSBusinessId_Practitioner]
	,[LDSBusinessId_ParentObservation]
	,[ClinicalEffectiveDate]
	,[LDSConceptId_DatePrecision]
	,[ResultValue]
	,[LDSConceptId_ResultUnits]
	,[ResultDate]
	,[ResultText]
	,[IsProblem]
	,[IsReview]
	,[ProblemEndDate]
	,[LDSConceptId_Source]
	,[AgeAtEvent]
	,[AgeAtEventBaby]
	,[AgeAtEventNeonate]
	,[LDSConceptId_Episodicity]
	,[IsPrimary]
	,[DateRecorded]
	,[IsDeleted]
	,[IsProblemDeleted]
	,[IsConfidential]
FROM (
	SELECT CO1.[LDSBusinessId]
		,CO1.[LDSBusinessKey]
		,CO1.[LDSRecordId]
		,CO1.[LDSDataSetId]
		,CO1.[RawStaged_EventId]
		,CO1.[ExtractStartTime]
		,CO1.[InitialExtractStartTime]
		,CO1.[LDSIsDeleted]
		,ISNULL(OR1.[ODSCode], 'Unknown') AS [RecordOwnerOrganisationCode]
		,P1.[LDSBusinessId] AS [LDSBusinessId_Patient]
		,CC1.[LDSBusinessId] AS [LDSBusinessId_Encounter]
		,UR1.[LDSBusinessId] AS [LDSBusinessId_Practitioner]
		,CO2.[LDSBusinessId] AS [LDSBusinessId_ParentObservation]
		,CO1.[EffectiveDate] AS [ClinicalEffectiveDate]
		,[LDSConceptId_DatePrecision] = CON1.[LDSBusinessId]
		,CO1.[Value] AS [ResultValue]
		,[LDSConceptId_ResultUnits] = CON2.[LDSBusinessId]
		,CO1.[EffectiveDate] AS [ResultDate]
		,CO1.[AssociatedText] AS [ResultText]
		,[IsProblem] = CASE 
			WHEN EXISTS (
					SELECT [LDSBusinessId]
					FROM [Sequenced_masked].[vPrimaryCareEMIS_CareRecord_Problem]
					)
				THEN CAST(1 AS BIT)
			ELSE CAST(0 AS BIT)
			END
		,[IsReview] = CASE 
			WHEN CO1.[Episode] = 'Review'
				THEN CAST(1 AS BIT)
			ELSE CAST(0 AS BIT)
			END
		,CP1.[EndDate] AS [ProblemEndDate]
		,[LDSConceptId_Source] = CON4.[LDSBusinessId]
		,[AgeAtEvent] = DATEDIFF(year, P1.[DateOfBirth], CO1.[EffectiveDate])
		,[AgeAtEventBaby] = CASE 
			WHEN DATEDIFF(day, P1.[DateOfBirth], CO1.[EffectiveDate]) < 1
				THEN 7001
			WHEN DATEDIFF(day, P1.[DateOfBirth], CO1.[EffectiveDate]) < 7
				THEN 7002
			WHEN DATEDIFF(day, P1.[DateOfBirth], CO1.[EffectiveDate]) < 29
				THEN 7003
			WHEN DATEDIFF(day, P1.[DateOfBirth], CO1.[EffectiveDate]) < 91
				THEN 7004
			WHEN DATEDIFF(day, P1.[DateOfBirth], CO1.[EffectiveDate]) < 182
				THEN 7005
			WHEN DATEDIFF(day, P1.[DateOfBirth], CO1.[EffectiveDate]) < 273
				THEN 7006
			WHEN DATEDIFF(day, P1.[DateOfBirth], CO1.[EffectiveDate]) < 366
				THEN 7007
			WHEN DATEDIFF(year, P1.[DateOfBirth], CO1.[EffectiveDate]) > 120
				THEN 120
			ELSE DATEDIFF(year, P1.[DateOfBirth], CO1.[EffectiveDate])
			END
		,[AgeAtEventNeonate] = CASE 
			WHEN DATEDIFF(day, P1.[DateOfBirth], CO1.[EffectiveDate]) <= 27
				THEN DATEDIFF(day, P1.[DateOfBirth], CO1.[EffectiveDate])
			ELSE NULL
			END
		,[LDSConceptId_Episodicity] = CON5.[LDSBusinessId]
		,[IsPrimary] = CASE 
			WHEN EXISTS (
					SELECT [ParentObservationGuid]
					FROM [Sequenced_masked].[vPrimaryCareEMIS_CareRecord_Observation]
					)
				THEN CAST(0 AS BIT)
			ELSE CAST(1 AS BIT)
			END
		,[DateRecorded] = CAST(CO1.[EnteredDate] AS DATETIME) + CAST(CO1.[EnteredTime] AS DATETIME)
		,CO1.[Deleted] AS [IsDeleted]
		,[IsProblemDeleted] = NULL
		,CO1.[IsConfidential]
	FROM [Sequenced_masked].[vPrimaryCareEMIS_CareRecord_Observation] CO1
	LEFT JOIN [Sequenced_masked].[vPrimaryCareEMIS_CareRecord_Problem] CP1
		ON CO1.[ObservationGuid] = CP1.[ObservationGuid]
			AND CP1.Deleted = 0
	LEFT JOIN [Sequenced_masked].[vPrimaryCareEMIS_Admin_UserInrole] UR1
		ON CO1.[ClinicianUserInRoleGuid] = UR1.[UserInRoleGuid]
	LEFT JOIN [Sequenced_masked].[vPrimaryCareEMIS_Admin_Patient] P1
		ON CO1.[PatientGuid] = P1.[PatientGuid]
			AND P1.Deleted = 0
	LEFT JOIN [Sequenced_masked].[vPrimaryCareEMIS_Admin_Organisation] OR1
		ON CO1.[OrganisationGuid] = OR1.[OrganisationGuid]
	LEFT JOIN [Sequenced_masked].[vPrimaryCareEMIS_CareRecord_Observation] CO2
		ON CO1.[ParentObservationGuid] = CO2.[ObservationGuid]
			AND CO2.Deleted = 0
	LEFT JOIN [Sequenced_masked].[vPrimaryCareEMIS_CareRecord_Consultation] CC1
		ON CO1.[ConsultationGuid] = CC1.[ConsultationGuid]
			AND CC1.Deleted = 0
	LEFT JOIN [Transform_masked].[PrimaryCareEMIS_Coding_ClinicalCode_Dedupe] CD1
		ON CO1.[CodeId] = CD1.[CodeId]
			AND CD1.[EmisCodeCategoryDescription] NOT IN ('Investigation Requests', 'Referral', 'Allergy and adverse reactions', 'Allergy and adverse drug reactions')
	LEFT JOIN [Terminology].[Concepts] CON1
		ON CAST(CO1.[EffectiveDatePrecision] AS VARCHAR(100)) = CON1.[Code]
			AND CON1.[System] = (
				SELECT CS1.[System]
				FROM Concept_Systems CS1
				WHERE CS1.[ColumnName] = 'EffectiveDatePrecision'
				)
	LEFT JOIN [Terminology].[Concepts] CON2
		ON CAST(CO1.[NumericUnit] AS VARCHAR(100)) = CON2.[Code]
			AND CON2.[System] = (
				SELECT CS1.[System]
				FROM Concept_Systems CS1
				WHERE CS1.[ColumnName] = 'NumericUnit'
				)
	LEFT JOIN [Terminology].[Concepts] CON4
		ON CAST(CO1.[CodeId] AS VARCHAR(100)) = CON4.[Code]
			AND CON4.[System] = (
				SELECT CS1.[System]
				FROM Concept_Systems CS1
				WHERE CS1.[ColumnName] = 'CodeId'
				)
	LEFT JOIN [Terminology].[Concepts] CON5
		ON CAST(CO1.[Episode] AS VARCHAR(100)) = CON5.[Code]
			AND CON5.[System] = (
				SELECT CS1.[System]
				FROM Concept_Systems CS1
				WHERE CS1.[ColumnName] = 'Episode'
				)
	) AS CO;
GO

[BillWoodNHS]

Pull request in place for the fix (PR2254)

[Rkw20067]

Retest in TEST (Release 167) - no orphaned records for Observations or Medication Orders.
To be retested by NCL in Pre-prod.

[EddieDavison92]

Seeing 81 medication_orders with a NULL "medication_order_core_concept_id" and zero observations with NULL patient_id