Skip to content

Instantly share code, notes, and snippets.

Chris Knoll chrisknoll

Block or report user

Report or block chrisknoll

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@chrisknoll
chrisknoll / datatableBinding.js
Created Oct 31, 2016
A datatable binding for knockout.js
View datatableBinding.js
// this AMD module assumes that datatables is configured as moduleID: 'datatables.net'
// This module also uses classes from font-awesome (fa-check-circle) to support the 'select' column
define(['jquery', 'knockout', 'datatables.net'], function ($, ko) {
function renderSelected(s, p, d) {
return '<span class="fa fa-check-circle"></span>';
}
function _getSelectedData(element)
{
@chrisknoll
chrisknoll / Calculate Percentiles
Created Jun 17, 2015
Large scale percentile calculation in SQL
View Calculate Percentiles
with rawData(count_value) as -- replace below query to get the values you would like to find percentiles of
(
select p.YEAR_OF_BIRTH
from dbo.PERSON p
),
overallStats (avg_value, stdev_value, min_value, max_value, total) as
(
select avg(1.0 * count_value) as avg_value,
stdev(count_value) as stdev_value,
min(count_value) as min_value,
@chrisknoll
chrisknoll / Condition Era Builder CDM V4
Created May 29, 2015
Builds condition eras from condition_occurrence from cdm v4. It does not do any sort of rollups (unlike Drug era where we roll up to ingredient)
View Condition Era Builder CDM V4
with cteConditionTarget (CONDITION_OCCURRENCE_ID, PERSON_ID, CONDITION_CONCEPT_ID, CONDITION_TYPE_CONCEPT_ID, CONDITION_START_DATE, CONDITION_END_DATE) as
(
select co.CONDITION_OCCURRENCE_ID, co.PERSON_ID, co.CONDITION_CONCEPT_ID, co.CONDITION_TYPE_CONCEPT_ID, co.CONDITION_START_DATE,
COALESCE(co.CONDITION_END_DATE, DATEADD(day,1,CONDITION_START_DATE)) as CONDITION_END_DATE
FROM CONDITION_OCCURRENCE co
),
cteEndDates (PERSON_ID, CONDITION_CONCEPT_ID, END_DATE) as -- the magic
(
select PERSON_ID, CONDITION_CONCEPT_ID, DATEADD(day,-30,EVENT_DATE) as END_DATE -- unpad the end date
FROM
@chrisknoll
chrisknoll / drug_exposure_eras_demo_NoUnboundPreceeding
Last active Jan 16, 2019
This SQL is simiar to the drugEraBuilder_demo, but does not use the ROWS UNBOUNDED PRECEDING windowing function. This makes this script work in databases where row_number() exists but doesn't support ROWS UNBOUNDED PRECEDING
View drug_exposure_eras_demo_NoUnboundPreceeding
-- Adapted from calculating concurrent sessions query found at http://sqlmag.com/t-sql/calculating-concurrent-sessions-part-3
-- Credits to Ben Flanaghan, Arnold Fribble, and R. Barry Young
-- DROP TABLE #DRUG_EXPOSURE;
CREATE TABLE #DRUG_EXPOSURE
(
[DRUG_EXPOSURE_ID] [bigint] NOT NULL,
[PERSON_ID] [bigint] NOT NULL,
[DRUG_CONCEPT_ID] [int] NOT NULL,
@chrisknoll
chrisknoll / Drug Era Builder v5
Last active Oct 13, 2018
This SQL script builds the CDM v5 DRUG_ERA table from drug exposures. It rolls up the drug exposures to the ingredient form, and then creates the eras for each ingredient concept id.
View Drug Era Builder v5
with cteDrugTarget (DRUG_EXPOSURE_ID, PERSON_ID, DRUG_CONCEPT_ID, DRUG_TYPE_CONCEPT_ID, DRUG_EXPOSURE_START_DATE, DRUG_EXPOSURE_END_DATE) as
(
-- Normalize DRUG_EXPOSURE_END_DATE to either the existing drug exposure end date, or add days supply, or add 1 day to the start date
select d.DRUG_EXPOSURE_ID, d. PERSON_ID, c.CONCEPT_ID, d.DRUG_TYPE_CONCEPT_ID, DRUG_EXPOSURE_START_DATE,
COALESCE(DRUG_EXPOSURE_END_DATE, DATEADD(day,DAYS_SUPPLY,DRUG_EXPOSURE_START_DATE), DATEADD(day,1,DRUG_EXPOSURE_START_DATE)) as DRUG_EXPOSURE_END_DATE
FROM DRUG_EXPOSURE d
join CONCEPT_ANCESTOR ca on ca.DESCENDANT_CONCEPT_ID = d.DRUG_CONCEPT_ID
join CONCEPT c on ca.ANCESTOR_CONCEPT_ID = c.CONCEPT_ID
where c.VOCABULARY_ID = 'RxNorm'
and c.CONCEPT_CLASS_ID = 'Ingredient'
@chrisknoll
chrisknoll / Drug Era Builder CDM V4
Created Apr 20, 2015
This script builds drug eras from CDM v4 DRUG_EXPOSURE tables and returns a result in the form of the DRUG_ERA table. The logic of the script will roll up drug exposures into their ingredient, and then apply the drug era building logic based on each ingredient concept. The drug exposure gap is set to 30 days.
View Drug Era Builder CDM V4
with cteDrugTarget (DRUG_EXPOSURE_ID, PERSON_ID, DRUG_CONCEPT_ID, DRUG_TYPE_CONCEPT_ID, DRUG_EXPOSURE_START_DATE, DRUG_EXPOSURE_END_DATE, INGREDIENT_CONCEPT_ID) as
(
-- Normalize DRUG_EXPOSURE_END_DATE to either the existing drug exposure end date, or add days supply, or add 1 day to the start date
select d.DRUG_EXPOSURE_ID, d. PERSON_ID, c.CONCEPT_ID, d.DRUG_TYPE_CONCEPT_ID, DRUG_EXPOSURE_START_DATE,
COALESCE(DRUG_EXPOSURE_END_DATE, DATEADD(day,DAYS_SUPPLY,DRUG_EXPOSURE_START_DATE), DATEADD(day,1,DRUG_EXPOSURE_START_DATE)) as DRUG_EXPOSURE_END_DATE,
c.CONCEPT_ID as INGREDIENT_CONCEPT_ID
FROM DRUG_EXPOSURE d
join CONCEPT_ANCESTOR ca on ca.DESCENDANT_CONCEPT_ID = d.DRUG_CONCEPT_ID
join CONCEPT c on ca.ANCESTOR_CONCEPT_ID = c.CONCEPT_ID
where c.VOCABULARY_ID = 8
@chrisknoll
chrisknoll / drugEraBuilder_demo
Last active Aug 29, 2015
Drug Era Builder SQL
View drugEraBuilder_demo
/*
Drug era build logic:
Christopher Knoll
Janssen R&D
PURPOSE:
This SQL example demonstrates how drug exposure events in the CDM database can be rolled up into eras by arranging
each drug exposure start_date a row_number() and lining it up with the overall row_number for all start_date and end_dates.
When the row_number() of an overall date = 2 * the row_number of a start, we have a 'closed era'.
You can’t perform that action at this time.