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
r.processing_method_manual = true
Count the total number of peaks in a particular project
SELECT count(*) FROM "lcuv_empower_v4_results_peaks" p
"lcuv_empower_v4_results" r
ON p.parent_uuid=r.uuid
"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 
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.