This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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) | |
{ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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'. |