Steve Harris 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

	-- 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.

	-- 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

	-- 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

	-- 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

	--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,

	# 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)

	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

	{
	"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"}
	}

	# 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)