Med Reconciliation approval per assessment
Clinical Field / Clinical QA approval for Med Reconciliation was stored once per patient, so a new recert showed the prior assessment's approval.
This adds the two approval columns to pAssessments on every agency_* database.
Important: Always backup databases before running migrations.
Backfill (only rows not yet set): each completed assessment takes the patient's current approval,
except one created after the patient's approval was last saved. Incomplete assessments start as not approved.
Safe to re-run: a Yes previously copied onto an incomplete assessment is cleared.
SQL preview
ALTER TABLE {agency_db}.pAssessments
ADD COLUMN `Med_Approve` INT NULL DEFAULT NULL,
ADD COLUMN `Med_Recon_Field` INT NULL DEFAULT NULL;
Migration Progress
SELECT SCHEMA_NAME AS db_name
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME LIKE 'agency\_%' ESCAPE '\\'
AND SCHEMA_NAME NOT LIKE '%lookup%'
ORDER BY SCHEMA_NAME
Found #getAgencies.recordCount# agency database(s)
SELECT
SUM(TABLE_NAME = 'pAssessments') AS has_assess,
SUM(TABLE_NAME = 'pPatients') AS has_patients
FROM information_schema.TABLES
WHERE TABLE_SCHEMA =
AND TABLE_NAME IN ('pAssessments', 'pPatients')
#db_name# — #rowMessage#
SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA =
AND TABLE_NAME = 'pAssessments'
AND COLUMN_NAME IN ('Med_Approve', 'Med_Recon_Field')
ALTER TABLE #db_name#.pAssessments ADD COLUMN `Med_Approve` INT NULL DEFAULT NULL
ALTER TABLE #db_name#.pAssessments ADD COLUMN `Med_Recon_Field` INT NULL DEFAULT NULL
UPDATE #db_name#.pAssessments a
JOIN (
SELECT p.Patient_ID,
IFNULL(p.Med_Approve, 0) AS Med_Approve,
IFNULL(p.Med_Recon_Field, 0) AS Med_Recon_Field,
p.Record_Mod_Date
FROM #db_name#.pPatients p
WHERE p.Status IN (0,1,4)
AND p.AutoPatient_ID = (
SELECT MAX(p2.AutoPatient_ID)
FROM #db_name#.pPatients p2
WHERE p2.Patient_ID = p.Patient_ID
AND p2.Status IN (0,1,4)
)
) pt ON pt.Patient_ID = a.Patient_ID
LEFT JOIN (
SELECT Assmt_ID, MIN(Record_Creation_Date) AS created_at
FROM #db_name#.pAssessments
GROUP BY Assmt_ID
) c ON c.Assmt_ID = a.Assmt_ID
SET a.Med_Approve = CASE
WHEN IFNULL(a.F25, 0) <> 1 THEN 0
WHEN c.created_at IS NOT NULL AND pt.Record_Mod_Date IS NOT NULL AND c.created_at > pt.Record_Mod_Date THEN 0
ELSE pt.Med_Approve END,
a.Med_Recon_Field = CASE
WHEN IFNULL(a.F25, 0) <> 1 THEN 0
WHEN c.created_at IS NOT NULL AND pt.Record_Mod_Date IS NOT NULL AND c.created_at > pt.Record_Mod_Date THEN 0
ELSE pt.Med_Recon_Field END
WHERE a.Status = 0
AND a.Med_Approve IS NULL
AND a.Med_Recon_Field IS NULL
UPDATE #db_name#.pAssessments
SET Med_Approve = IF(Med_Approve = 1, 0, Med_Approve),
Med_Recon_Field = IF(Med_Recon_Field = 1, 0, Med_Recon_Field)
WHERE Status = 0
AND IFNULL(F25, 0) <> 1
AND (Med_Approve = 1 OR Med_Recon_Field = 1)
#db_name# — #htmlEditFormat(rowMessage)#
#db_name# — #htmlEditFormat(rowMessage)#
Failed to list agency databases: #htmlEditFormat(cfcatch.message)#
Summary: Done #totalSuccess# | Errors #totalErrors#
| Database | Status | Message |
|---|
| #htmlEditFormat(r.db_name)# |
#htmlEditFormat(r.status)# |
#htmlEditFormat(r.message)# |
Back to Migrations