docsteveharris

-- Example script 
-- to pick out patients currently in A&E resus or majors

SELECT
   vd.location_visit_id
  ,vd.hospital_visit_id
  ,vd.location_id
  -- ugly HL7 location string 
  ,lo.location_string
  -- time admitted to that bed/theatre/scan etc.

docsteveharris

-- example script to extract stroke diagnoses
-- concept_id pulled from https://athena.ohdsi.org/search-terms/start by searching for ICD or OPCS codes
-- e.g. OPCS U54.3 --> https://athena.ohdsi.org/search-terms/terms/44514242

SELECT 
	c.* 
FROM dsf_omop.condition_occurrence c
LEFT JOIN dsf_omop.concept co ON c.condition_concept_id = co.concept_id
WHERE 
	-- see https://github.com/spiros/chronological-map-phenotypes/blob/master/secondary_care/ICD_Isch_stroke.csv

docsteveharris

-- P of PICO looks like
-- Lymphocyte count less than 1.2 within 72 hours of critical care admission + mechanical ventilation.
-- O 60-day mortality…. So in hospital mortality would be great

-- 1. find lymphocyte counts

SELECT 
*
FROM dsf_omop.measurement m 
WHERE m.measurement_concept_id = 3019198

docsteveharris

Doctors and nurses make many treatment decisions. Only some are guided by gold standard evidence from randomised controlled trials (an RCT). Many are made without evidence and must rely on training and experience. There is often consensus for 'big decisions' but for smaller decisions, since each clinician's training and experience is different, there is frequent arbitrary variation.

For example, most clinicians will agree that prolonged antibiotics for an infection eventually cause more harm than good. The patient will develop side effects, and the microbes become resistant. However, there is no gold standard to say how many hours or days of each antibiotics are enough for each different infection.

Traditional RCTs are too expensive to answer all these questions. We have made some progress in reducing cost by embedding the RCT in an electronic health record (EHR) so that data collection and follow-up are then automated. However, consenting and randomising patients still requires a research nurse to visit ea

docsteveharris

-- Current occupancy of the Hospital
SELECT ward,
  count(ward) as occupancy
  FROM (
SELECT DISTINCT ON (pp.value_as_string)
  m.mrn AS Mrn
  , pa.value_as_datetime AS Allocation_Time
  , pp.value_as_string AS Bed_Name
  , split_part(split_part(pp.value_as_string, E'^', 2), ' ', 1) AS ward
FROM live.patient_property pp

docsteveharris

--SELECT * FROM omop_live.care_site WHERE care_site_name LIKE 'T03%' order by care_site_name;
--JOIN done manually because otherwise runs really slowly

SELECT 
    vd.person_id,
    vd.visit_detail_id,
    vd.visit_occurrence_id,
--    cs.care_site_id,
--    cs.care_site_name,
    vd.visit_start_datetime,

docsteveharris

# 2019-11-24
# Steve Harris
# TODO the care site table will need updating; it was exported on the date above but will inevitably change with time

# CRAN libraries
library(data.table)
library(assertthat)
library(readr)
library(stringr)

docsteveharris

select * from (

select 0 as id, 'INFO' as type , 'Number of runs' as note, count(*)

from omop_live.etl_error_log

where error_message like 'Finishe%'

union

docsteveharris

{
  "config": {"view": {"width": 400, "height": 300}, "mark": {"tooltip": null}},
  "layer": [
    {
      "mark": {"type": "line", "interpolate": "basis"},
      "encoding": {
        "color": {"type": "nominal", "field": "messagetype"},
        "x": {"type": "temporal", "field": "messagedatetime"},
        "y": {"type": "quantitative", "field": "value"}
      }

docsteveharris

# Prepare wide data for 3D printing
# Expects to be passed 3 columns: x,y,z
# x and y will be binned
# a function of z will be used to prepare the z coordinate

library(data.table)
library(ggplot2)
library(r2stl)
library(Hmisc)
library(assertthat)