DELETE FROM #Request.prefix_db_lookup#.cron_GetTotals
UPDATE #Request.prefix_db_agency#.patient
SET Missing_DOC_ID = 0
SELECT * FROM #Request.prefix_db_agency#.DocumentDesc
WHERE Required = 1
AND DocumentType = 'Patient'
Insert IGNORE INTO #Request.prefix_db_lookup#.cron_GetTotals
(
NumField1,NumField2
)
SELECT Distinct(patient.Patient_ID),Missing_DOC_ID
FROM #Request.prefix_db_agency#.patient
WHERE patient.Patient_ID NOT IN (SELECT distinct(files.PatientID)
FROM #Request.prefix_db_agency#.files WHERE files.DOC_ID = #DOC_ID#)
AND patient.Status = 'active'
GROUP BY patient.Patient_ID
UPDATE #Request.prefix_db_agency#.patient ,#Request.prefix_db_lookup#.cron_GetTotals
set patient.Missing_DOC_ID = '#DOC_ID#'
where patient.Patient_ID =cron_GetTotals.NumField1
AND cron_GetTotals.NumField2 = 0 ;
UPDATE #Request.prefix_db_agency#.patient ,#Request.prefix_db_lookup#.cron_GetTotals
set patient.Missing_DOC_ID = concat(Missing_DOC_ID, ',','#DOC_ID#')
where patient.Patient_ID =cron_GetTotals.NumField1
AND cron_GetTotals.NumField2 != 0 ;
DELETE FROM #Request.prefix_db_lookup#.cron_GetTotals
SELECT * FROM #Request.prefix_db_agency#.DocumentDesc
WHERE Required = 1
AND DocumentType = 'Patient'
Missing Patient Documents
Patient
SELECT *
FROM #Request.prefix_db_agency#.patient
WHERE patient.Status = 'active'
AND Missing_DOC_ID !=0
GROUP BY patient.Patient_ID
SELECT Distinct(patient.Patient_ID), patient.Agency_ID, patient.Last_Name_M0040 AS PatientLast, patient.First_Name_M0040 AS PatientFirst, patient.Status
FROM #Request.prefix_db_agency#.patient
WHERE NOT EXISTS (SELECT files.PatientId
FROM #Request.prefix_db_agency#.files, #Request.prefix_db_agency#.DocumentDesc
WHERE files.PatientId = patient.Patient_ID
AND files.DOC_ID = DocumentDesc.DOC_ID
AND DocumentDesc.DocumentType = 'Patient'
and DocumentDesc.Required = 1)
and patient.Status = 'active'