Empower Example SQL Queries

The following are sample SQL queries for viewing data.

/*
Find the number of injections that has manually processed results for a given project
*/
SELECT COUNT(DISTINCT(i.uuid)) FROM "lcuv_empower_v4_injection" i
LEFT JOIN "lcuv_empower_v4_results" r
ON i.uuid = r.parent_uuid
WHERE
r.processing_method_manual = true
AND
i.project='EDSL__Waters__Data__Acquity__Multi__inj_Aug20_FAT';
/* 
Count the total number of peaks in a particular project
*/
SELECT count(*) FROM "lcuv_empower_v4_results_peaks" p
LEFT JOIN 
"lcuv_empower_v4_results" r
ON p.parent_uuid=r.uuid
LEFT JOIN
"lcuv_empower_v4_injection" i 
ON r.parent_uuid=i.uuid
WHERE i.project_name='EDSL Waters Data\Acquity Multi inj_Aug20_FAT';
/*
It is important to understand System Utilization as it pertains to Maintenance and Suitability.  The following query will order by the systems with the longest runtimes.  This query shows data in the last 6 months.
*/

select i1.system_name, sum(i1.run_time_value) as total_time, i1.run_time_unit
from "lcuv_empower_v3_injection" i1
left join 
"lcuv_empower_v3_injection" i2
on i2.uuid=i1.uuid 
where 
from_iso8601_timestamp(i2.time_acquisition) > date_add('month', -6, current_date)
group by i1.system_name, i1.run_time_unit
order by total_time desc

/*
This insight could potentially help the lab balance their runs better to gain greater utilization across all instruments.  We could even go deeper and look at the column utilization to better predict when maintenance will be due.
*/