Andrew Charlwood
37 lines
1.1 KiB
SQL
37 lines
1.1 KiB
SQL
/*
|
|
Latest data dates
|
|
=================
|
|
|
|
Purpose:
|
|
Check freshness anchors before deciding reporting periods.
|
|
|
|
Notes:
|
|
- Dispensing uses ProcessingPeriodDate and usually lags.
|
|
- Unified prescribing DateMedicationStart can include future starts, so the
|
|
TPP DateEventRecorded probe is included as a more conservative source
|
|
event freshness marker.
|
|
*/
|
|
|
|
SELECT
|
|
'NATIONAL.GPMED.MedicinesDispensedInPrimarycare' AS "Source",
|
|
MAX("ProcessingPeriodDate")::DATE AS "LatestDate"
|
|
FROM NATIONAL.GPMED."MedicinesDispensedInPrimarycare"
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
'REPORTING_DATASETS_ICB.SCRATCHPAD.MEDS__UnifiedPrescribingTable DateMedicationStart' AS "Source",
|
|
MAX("DateMedicationStart")::DATE AS "LatestDate"
|
|
FROM REPORTING_DATASETS_ICB.SCRATCHPAD."MEDS__UnifiedPrescribingTable"
|
|
WHERE "DateMedicationStart" <= CURRENT_DATE()
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
'PRIMARY_CARE.TPP.SRPrimaryCareMedication DateEventRecorded' AS "Source",
|
|
MAX(CAST("DateEventRecorded" AS DATE)) AS "LatestDate"
|
|
FROM PRIMARY_CARE.TPP."SRPrimaryCareMedication"
|
|
WHERE "DateEventRecorded" >= DATEADD('MONTH', -3, CURRENT_DATE())
|
|
ORDER BY "Source";
|
|
|