One of the more enjoyable aspects of my current job is navigating the in house Laboratory Information Management System (or LIMS) in order to validate (or disprove) our assumptions about trends, find cases, and just plain find interesting numbers. (Example: Our highest BAC in a DUI case was 0.562). Because our LIMS was coded completely in house I had access to the developer, and through him the DB Schema. The LIMS would allow us to execute sanitized queries, and for more complex operations we used pgAdmin III.
We were curious if change laws affected the frequency of specific testing requests. This query uses two basic sub-queries, and then joins them by date.
Test Type % Per Month
WITH
THC_Elisa as (
SELECT to_char(offense_date, 'YYYY/MM') as month, count(to_char(offense_date, 'MM/YYYY')) as number
FROM requests r INNER JOIN tests t
ON t.request=r.id
INNER JOIN testtypes tt
ON tt.id=t.type
WHERE request_type='lawenforcement'
AND r.active
AND r.tests_finished
AND tt.analyte='Cannabinoids'
GROUP BY to_char(offense_date, 'YYYY/MM')
ORDER BY to_char(offense_date, 'YYYY/MM') desc
),
All_LEO as(
SELECT to_char(offense_date, 'YYYY/MM') as month, count(to_char(offense_date, 'MM/YYYY')) as number
FROM requests
WHERE request_type='lawenforcement'
AND active
AND tests_finished
GROUP BY to_char(offense_date, 'YYYY/MM')
ORDER BY to_char(offense_date, 'YYYY/MM') desc
)
SELECT All_LEO.month, All_LEO.number as all, THC_Elisa.number as "THC Screen total"
FROM All_LEO INNER JOIN THC_Elisa ON All_LEO.month=THC_Elisa.month
This one started out with a chance meeting in a court house, and ended with our clients using this information to share tactics and improve their accuracy. This one generates a chart of the last year of results of a certain kind of test, sorts them by client, and categorizes them by industry standards. This list (after some sanitation and processing) was distributed to our clients and gave them metrics to evaluate their practices.
Results by client
SELECT e1.name, AVG(r1.result), COUNT(r1.result), COUNT(CASE WHEN (r1.result > '0.080') THEN 1 ELSE NULL END), COUNT(CASE WHEN (r1.result > '0.30') THEN 1 ELSE NULL END)
FROM
(SELECT
e.name as NAME, e.id AS id, t1.result as result
FROM entities e
INNER JOIN requests r on r.requestor=e.id
INNER JOIN
( SELECT
CASE
WHEN t.result IN ('<0.003','none detected','<0.010')
THEN '0'
ELSE
t.result::numeric
END result,
t.request
FROM tests t
INNER JOIN testtypes tt on t.type = tt.id
WHERE tt.analyte='Ethyl Alcohol'
AND t.testdate > (NOW() - INTERVAL '1 YEAR')
AND t.active
) as t1 ON t1.request=r.id
WHERE e.type='lawenforcement'
AND e.state='CO' ) as r1 INNER JOIN
(SELECT p.id, e.name
FROM
(
SELECT id as id,
(
CASE
WHEN parent_entity IS NULL
THEN id
ELSE parent_entity
END
) AS parent
FROM entities
ORDER BY id
) as p INNER JOIN entities e on p.parent=e.id
) as e1 on r1.id=e1.id
GROUP BY e1.name
ORDER BY COUNT(r1.result) DESC
Post a Comment