The Analytics page on the SPM dashboard contains built-in tools to examine a variety of sample monitoring metrics. These tools include:
•Monitoring Completed
•Monitoring Status by Type
•Monitoring Not Conducted
•Monitoring Task Completion
•Monitoring Priority
Monitoring Completed
Shows number of completed samples based on facility.
SQL:
with
fg as (select f.facility_id
,f.facility_code
from equis.facility_group_members(@facilityId) f
),
t as (SELECT COUNT(1) AS S1, MAX('Completed') Status, s.facility_id
FROM dt_sample s
WHERE (NOT (UPPER(s.sample_type_code) LIKE 'NST%')) AND
(s.sample_date >= @startDate AND s.sample_date <= @endDate)
GROUP BY s.facility_id )
select t.s1, Coalesce(ff.facility_name,fg.facility_code) name, t.status
from t
inner join fg on
fg.facility_id=t.facility_id
inner join dt_facility ff on
ff.facility_id=t.facility_id
Monitoring Status by Type
Shows the status of samples by type. The user can drilldown to see the status, and can drilldown further to see the related facility.
SQL:
SELECT COUNT(1) AS S1, COALESCE(t.task_type, 'No Task Type') AS task_type, MAX('Complete') Status, COALESCE(f.facility_name, f.facility_code) AS facility_name
FROM dt_sample s INNER JOIN
dt_task t ON s.facility_id = t.facility_id AND s.facility_id = t.facility_id AND s.task_code = t.task_code
INNER JOIN dt_facility f ON f.facility_id = s.facility_id
WHERE (NOT (UPPER(s.sample_type_code) LIKE 'NST%')) AND
(s.sample_date >= @startDate AND s.sample_date <= @endDate) AND
f.facility_id = @facilityId
GROUP BY t.task_type, f.facility_code, f.facility_name
UNION
SELECT COUNT(1) S1, COALESCE(t.task_type, 'No Task Type') AS task_type, MAX('Visited not available') Status, COALESCE(f.facility_name, f.facility_code) AS facility_name
FROM dt_sample s INNER JOIN
dt_task t ON s.facility_id = t.facility_id AND s.facility_id = t.facility_id AND s.task_code = t.task_code
INNER JOIN dt_facility f ON f.facility_id = s.facility_id
WHERE (UPPER(s.sample_type_code) = 'NST') AND
(s.sample_date >= @startDate AND s.sample_date <= @endDate) AND
f.facility_id = @facilityId
GROUP BY t.task_type, f.facility_code, f.facility_name
UNION
SELECT COUNT(1) S1, COALESCE(t.task_type, 'No Task Type') AS task_type, MAX('Visited not available') Status, COALESCE(f.facility_name, f.facility_code) AS facility_name
FROM dt_sample s INNER JOIN
dt_task t ON s.facility_id = t.facility_id AND s.facility_id = t.facility_id AND s.task_code = t.task_code
INNER JOIN dt_facility f ON f.facility_id = s.facility_id
WHERE (UPPER(s.sample_type_code) = 'NST-NV') AND
(s.sample_date >= @startDate AND s.sample_date <= @endDate) AND
f.facility_id = @facilityId
GROUP BY t.task_type, f.facility_code, f.facility_name
Monitoring Not Conducted
Shows samples that were not conducted. Can drilldown to field qualifier and then facilities.
SQL:
SELECT COUNT(1) S1, COALESCE(t.monitor_type_lookup_code, 'No Lookup Code') AS monitor_type_lookup_code, COALESCE(fs.fld_qualifier, 'No Field Qualifier') AS field_qualifier, COALESCE(f.facility_name, f.facility_code) AS facility_name
FROM dt_sample s INNER JOIN
dt_task t ON s.facility_id = t.facility_id AND s.facility_id = t.facility_id AND s.task_code = t.task_code INNER JOIN
dt_field_sample fs ON s.facility_id = fs.facility_id AND s.sample_id = fs.sample_id
INNER JOIN dt_facility f ON s.facility_id = f.facility_id
WHERE (UPPER(s.sample_type_code) like 'NST%') AND
(s.sample_date >= @startDate AND s.sample_date <= @endDate) AND
f.facility_id = @facilityId
GROUP BY t.monitor_type_lookup_code, f.facility_code, fs.fld_qualifier, f.facility_name
Monitoring Task Completion
Shows status of samples based on task type. Can drilldown to status and then facility.
SQL:
SELECT COUNT(1) AS S1, COALESCE(t.monitor_type_lookup_code, 'No Monitoring Type Code') as monitor_type_lookup_code, 'Complete' Status, COALESCE(f.facility_name, f.facility_code) AS facility_name
FROM dt_sample s INNER JOIN
dt_task t ON s.facility_id = t.facility_id AND s.facility_id = t.facility_id AND s.task_code = t.task_code
INNER JOIN dt_facility f ON s.facility_id = f.facility_id
WHERE (NOT (UPPER(s.sample_type_code) LIKE 'NST%')) AND
(s.sample_date >= @startDate AND s.sample_date <= @endDate) AND
f.facility_id = @facilityId
GROUP BY t.monitor_type_lookup_code, f.facility_code, f.facility_name
UNION
SELECT COUNT(1) S1, COALESCE(t.monitor_type_lookup_code, 'No Monitoring Type Code') as monitor_type_lookup_code, 'Visited not available' Status, COALESCE(f.facility_name, f.facility_code) AS facility_name
FROM dt_sample s INNER JOIN
dt_task t ON s.facility_id = t.facility_id AND s.facility_id = t.facility_id AND s.task_code = t.task_code
INNER JOIN dt_facility f ON s.facility_id = f.facility_id
WHERE (UPPER(s.sample_type_code) = 'NST') AND
(s.sample_date >= @startDate AND s.sample_date <= @endDate) AND
f.facility_id = @facilityId
GROUP BY t.monitor_type_lookup_code, f.facility_code, f.facility_name
UNION
SELECT COUNT(1) S1, COALESCE(t.monitor_type_lookup_code, 'No Monitoring Type Code') as monitor_type_lookup_code, 'Not Conducted' Status, COALESCE(f.facility_name, f.facility_code) AS facility_name
FROM dt_sample s INNER JOIN
dt_task t ON s.facility_id = t.facility_id AND s.facility_id = t.facility_id AND s.task_code = t.task_code
INNER JOIN dt_facility f ON s.facility_id = f.facility_id
WHERE (UPPER(s.sample_type_code) = 'NST-NV') AND
(s.sample_date >= @startDate AND s.sample_date <= @endDate) AND
f.facility_id = @facilityId
GROUP BY t.monitor_type_lookup_code, f.facility_code, f.facility_name
Monitoring Priority
Shows the priority of the samples based on the facility.
SQL:
SELECT COUNT(1) AS S1, MAX('Complete') Status, COALESCE(f.facility_name, f.facility_code) AS facility_name, t.delivery_order
FROM dt_sample s INNER JOIN
dt_task t ON s.facility_id = t.facility_id AND s.facility_id = t.facility_id AND s.task_code = t.task_code
INNER JOIN dt_facility f ON f.facility_id = s.facility_id
WHERE (NOT (UPPER(s.sample_type_code) LIKE 'NST%')) AND
(s.sample_date >= @startDate AND s.sample_date <= @endDate) AND
t.delivery_order IS NOT NULL AND
f.facility_id = @facilityId
GROUP BY f.facility_code, t.delivery_order, f.facility_name
UNION
SELECT COUNT(1) S1, MAX('Visited not available') Status, COALESCE(f.facility_name, f.facility_code) AS facility_name, t.delivery_order
FROM dt_sample s INNER JOIN
dt_task t ON s.facility_id = t.facility_id AND s.facility_id = t.facility_id AND s.task_code = t.task_code
INNER JOIN dt_facility f ON f.facility_id = s.facility_id
WHERE (UPPER(s.sample_type_code) = 'NST') AND
(s.sample_date >= @startDate AND s.sample_date <= @endDate) AND
t.delivery_order IS NOT NULL AND
f.facility_id = @facilityId
GROUP BY f.facility_code, t.delivery_order, f.facility_name
UNION
SELECT COUNT(1) S1, MAX('Visited not available') Status, COALESCE(f.facility_name, f.facility_code) AS facility_name, t.delivery_order
FROM dt_sample s INNER JOIN
dt_task t ON s.facility_id = t.facility_id AND s.facility_id = t.facility_id AND s.task_code = t.task_code
INNER JOIN dt_facility f ON f.facility_id = s.facility_id
WHERE (UPPER(s.sample_type_code) = 'NST-NV') AND
(s.sample_date >= @startDate AND s.sample_date <= @endDate) AND
t.delivery_order IS NOT NULL AND
f.facility_id = @facilityId
GROUP BY f.facility_code, t.delivery_order, f.facility_name