Analytics

<< Click to Display Table of Contents >>

EQuIS 7  >>  SPM > SPM Enterprise >

Analytics

The Analytics page of 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 amount 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 drill down to see the Status, and can drill down 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

MonitoringType-20010

 

Monitoring Not Conducted: Shows samples that were not conducted. Can drill down 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

MonitoringNotConducted-20010

 

Monitoring Task Completion: Shows Status of Samples based on Task Type. Can drill down 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

MonitoringCompleteness-20100