-
Notifications
You must be signed in to change notification settings - Fork 6
/
CreateCohortsAcuteEvaluation.sql
179 lines (171 loc) · 7.25 KB
/
CreateCohortsAcuteEvaluation.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
{DEFAULT @cdm_database_schema = 'CDM_SIM' }
{DEFAULT @cohort_database_schema = 'CDM_SIM' }
{DEFAULT @cohort_database_table = 'cohort'
{DEFAULT @work_database_schema = 'CDM_SIM' }
{DEFAULT @x_spec_cohort = 0 }
{DEFAULT @tempDB = "scratch.dbo" }
{DEFAULT @test_cohort = "test_cohort" }
{DEFAULT @ageLimit = 0}
{DEFAULT @upperAgeLimit = 120}
{DEFAULT @gender = c(8507, 8532)}
{DEFAULT @race = 0}
{DEFAULT @ethnicity = 0}
{DEFAULT @startDate = '19000101' }
{DEFAULT @endDate = '21000101' }
{DEFAULT @baseSampleSize = 150000 }
{DEFAULT @xSpecSampleSize = 1500 }
{DEFAULT @mainPopnCohort = 0 }
{DEFAULT @mainPopnCohortStartDay = 0 }
{DEFAULT @mainPopnCohortEndDay = 0 }
{DEFAULT @exclCohort = 0 }
{DEFAULT @visitLength = 0 }
{DEFAULT @visitType = c(9201) }
{DEFAULT @firstCut = FALSE }
DROP TABLE IF EXISTS #cohort_person;
SELECT *
INTO #cohort_person
FROM (
SELECT co.*,
p.*,
row_number() OVER (
ORDER BY NewId()
) rn
FROM @cohort_database_schema.@cohort_database_table co
JOIN @cdm_database_schema.person p
ON co.subject_id = p.person_id
AND year(COHORT_START_DATE) - year_of_birth >= @ageLimit
AND year(COHORT_START_DATE) - year_of_birth <= @upperAgeLimit
AND gender_concept_id IN (@gender)
{@race != 0} ? {AND race_concept_id in (@race)}
{@ethnicity != 0} ? {AND ethnicity_concept_id in (@ethnicity)}
WHERE cohort_definition_id = @x_spec_cohort
AND co.COHORT_START_DATE >= cast('@startDate' AS DATE)
AND co.COHORT_START_DATE <= cast('@endDate' AS DATE)
) pos;
DROP TABLE IF EXISTS @work_database_schema.@test_cohort;
select CAST(0 AS BIGINT) as COHORT_DEFINITION_ID, person_id as SUBJECT_ID,
dateadd(day, 0, visit_start_date) COHORT_START_DATE,
dateadd(day, 1, visit_start_date) COHORT_END_DATE
INTO @work_database_schema.@test_cohort
from (select
{@mainPopnCohort == 0} ? {
v.person_id, FIRST_VALUE(visit_start_date) OVER (PARTITION BY v.person_id ORDER BY NewId()) visit_start_date,
row_number() over (order by NewId()) rn
from @cdm_database_schema.visit_occurrence v
JOIN @cdm_database_schema.observation_period obs
on v.person_id = obs.person_id
AND v.visit_start_date >= dateadd(d, 365, obs.observation_period_start_date)
AND v.visit_start_date <= dateadd(d, -30, obs.observation_period_end_date)
join (
select person_id,
datediff(day, min(observation_period_start_date), min(observation_period_end_date)) lenPd,
min(observation_period_start_date) observation_period_start_date,
min(observation_period_end_date) observation_period_end_date,
count(observation_period_id) cntPd
from @cdm_database_schema.observation_period
group by person_id) obs2
on v.person_id = obs2.person_id
and v.visit_start_date >= obs2.observation_period_start_date
and v.visit_start_date <= obs2.observation_period_end_date
and lenPd >= 730
and cntPd = 1
join @cdm_database_schema.person p
on v.person_id = p.person_id
and year(visit_start_date) - year_of_birth >= @ageLimit
and year(visit_start_date) - year_of_birth <= @upperAgeLimit
and gender_concept_id in (@gender)
{@race != 0} ? {AND race_concept_id in (@race)}
{@ethnicity != 0} ? {AND ethnicity_concept_id in (@ethnicity)}
join (
select person_id,
datediff(day, min(observation_period_start_date), min(observation_period_end_date)) lenPd,
min(observation_period_start_date) observation_period_start_date,
min(observation_period_end_date) observation_period_end_date,
count(observation_period_id) cntPd
from @cdm_database_schema.observation_period
group by person_id) obs2
on v.person_id = obs2.person_id
and v.visit_start_date >= obs2.observation_period_start_date
and v.visit_start_date <= obs2.observation_period_end_date
and lenPd >= 730
and cntPd = 1
{@exclCohort != 0} ? { -- exclude subjects in the xSens cohort
left join @cohort_database_schema.@cohort_database_table excl
on v.person_id = excl.subject_id
and v.visit_start_date = excl.COHORT_START_DATE
}
where visit_start_date >= cast('@startDate' AS DATE)
and visit_start_date <= cast('@endDate' AS DATE)
and v.visit_concept_id in (@visitType)
and datediff(day, visit_start_date, visit_end_date) >= @visitLength
{@firstCut} ? {and 11*(9*(v.visit_occurrence_id/9)/11) = v.visit_occurrence_id}
{@exclCohort != 0} ? { -- exclusion = did not match on the above left join
and excl.subject_id is NULL
}
}
{@mainPopnCohort != 0} ? {
--co.subject_id as person_id, v.visit_start_date,
-- row_number() over (order by NewId()) rn
co.subject_id as person_id, FIRST_VALUE(v.visit_start_date) OVER (PARTITION BY v.person_id ORDER BY NewId()) visit_start_date,
row_number() over (order by NewId()) rn
from @cohort_database_schema.@cohort_database_table co
join @cdm_database_schema.visit_occurrence v
on v.person_id = co.subject_id
and v.visit_concept_id in (@visitType)
and v.visit_start_date >= dateadd(day, @mainPopnCohortStartDay, co.COHORT_START_DATE)
and v.visit_start_date <= dateadd(day, @mainPopnCohortEndDay, co.COHORT_START_DATE)
and v.visit_start_date >= cast('@startDate' AS DATE)
and v.visit_start_date <= cast('@endDate' AS DATE)
join (
select person_id,
datediff(day, min(observation_period_start_date), min(observation_period_end_date)) lenPd,
min(observation_period_start_date) observation_period_start_date,
min(observation_period_end_date) observation_period_end_date,
count(observation_period_id) cntPd
from @cdm_database_schema.observation_period
group by person_id) obs2
on v.person_id = obs2.person_id
and v.visit_start_date >= obs2.observation_period_start_date
and v.visit_start_date <= obs2.observation_period_end_date
and lenPd >= 730
and cntPd = 1
join @cdm_database_schema.person p
on co.subject_id = p.person_id
and year(co.COHORT_START_DATE) - year_of_birth >= @ageLimit
and year(co.COHORT_START_DATE) - year_of_birth <= @upperAgeLimit
and gender_concept_id in (@gender)
{@race != 0} ? {AND race_concept_id in (@race)}
{@ethnicity != 0} ? {AND ethnicity_concept_id in (@ethnicity)}
{@exclCohort != 0} ? {
left join @cohort_database_schema.@cohort_database_table excl
on v.person_id = excl.subject_id
and v.visit_start_date = excl.COHORT_START_DATE
}
where co.cohort_definition_id = @mainPopnCohort
{@exclCohort != 0} ? {
and excl.subject_id is NULL
}
}
) negs
where rn <= cast('@baseSampleSize' as bigint)
union
select 0 as COHORT_DEFINITION_ID, SUBJECT_ID, cp.COHORT_START_DATE COHORT_START_DATE,
dateadd(day, 1, cp.COHORT_START_DATE) COHORT_END_DATE
from #cohort_person cp
join @cdm_database_schema.observation_period o
on cp.SUBJECT_ID = o.person_id
and cp.COHORT_START_DATE >= o.observation_period_start_date
and cp.COHORT_START_DATE <= o.observation_period_end_date
where rn <= @xSpecSampleSize
union
select @x_spec_cohort as COHORT_DEFINITION_ID, SUBJECT_ID, cp.COHORT_START_DATE COHORT_START_DATE,
dateadd(day, 1, cp.COHORT_START_DATE) COHORT_END_DATE
from #cohort_person cp
join @cdm_database_schema.observation_period o
on cp.SUBJECT_ID = o.person_id
and cp.COHORT_START_DATE >= o.observation_period_start_date
and cp.COHORT_START_DATE <= o.observation_period_end_date
where rn <= @xSpecSampleSize
;
TRUNCATE TABLE #cohort_person;
DROP TABLE #cohort_person;