GraphicalGuidelines/Example 1.sql at master · hauserrg/GraphicalGuidelines · GitHub

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
/*
This file accompanies the manuscript titled:
	Graphical Analysis of Guideline Adherence Finds Systemwide Anomalies in HIV Diagnostic Testing

Specifically it follows the first example from the manuscript's supplement.

The codes creates these tables listed below then it performs analysis on the tables.

The nodes in this example represent tests performed.

Tables:
	select * from #Nodes
	select * from #NodesClone
	select * from #NodesPartition
	select * from #NodesCloneWithFacility
	select * from #Edges

Ronald "George" Hauser
Accompanied by Ankur Bhargava
2019-05-09
*/

--Data input
IFOBJECT_ID('tempdb..#Nodes', 'U') ISNOTNULLDROPTABLE #Nodes
CREATETABLE #Nodes
(
	NodeId intidentity(1,1),
	PatientId varchar(100),
	NodeType varchar(100),
	NodeDt datetime,
	FacilityId int, --optional
--ClinicianId int --optional, similar to FacilityId
);

IFOBJECT_ID('tempdb..#Adjudications', 'U') ISNOTNULLDROPTABLE #Adjudications
CREATETABLE #Adjudications
(
	NodeType1 varchar(100),
	NodeType2 varchar(100),
	AllowedQ bit
);

insertinto #Nodesvalues--<-- This is table S1 in the manuscript supplement.
(1,1,'2000-01-01', 1),
(1,2,'2000-10-01', 1),
(2,1,'2000-05-01', 1),
(2,2,'2000-06-01', 1),
(3,2,'2000-03-01', 2)

insertinto #Adjudications values
('Start','1',1),
('1','2',1),
('2','End',1),
('Start','2',0)

-------------------- Step 1 - Add Start/End nodes
IFOBJECT_ID('tempdb..#StartEnd', 'U') ISNOTNULLDROPTABLE #StartEnd
createtable #StartEnd(	StartEnd varchar(100) )
insertinto #StartEnd values ('Start'),('End')

IFOBJECT_ID('tempdb..#NodesClone', 'U') ISNOTNULLDROPTABLE #NodesClone
select NodeId, PatientId, NodeType, NodeDt
into #NodesClone
from #Nodes

insertinto #NodesClone
selectt.PatientId, #StartEnd.StartEnd NodeId, casewhen #StartEnd.StartEnd='Start'thencast('1753-1-1'asdatetime) else'9999-12-31 23:59:59.997'end NodeDt
from (
select distinct PatientId
from #NodesClone
) t
crossapply #StartEnd
-------------------- Step 2 - Convert nodes to edges
IFOBJECT_ID('tempdb..#NodesPartition', 'U') ISNOTNULLDROPTABLE #NodesPartition
select*, row_number() over(partitionby PatientId order by NodeDt) PatientChronoId
into #NodesPartition
from #NodesClone

IFOBJECT_ID('tempdb..#Edges', 'U') ISNOTNULLDROPTABLE #Edges
selectnp1.NodeId NodeId1, np2.NodeId NodeId2
into #Edges
from #NodesPartition np1
join #NodesPartition np2 onnp1.PatientId=np2.PatientIdandnp1.PatientChronoId+1=np2.PatientChronoId

/* Check to ensure no two patient NodeDts are the same */
declare @d1 int= (selectcount(1) [Count] from (select distinct PatientId, NodeDt from #NodesClone) t)
declare @d2 int= (selectcount(1) [Count] from #NodesClone)
if (@d1 != @d2) throw50000, 'Nodes for a patient occur at the same time. The chronological order is ambiguous.', 0

-------------------- Step 3 - Add Facility to #NodesClone table
IFOBJECT_ID('tempdb..#NodesCloneWithFacility', 'U') ISNOTNULLDROPTABLE #NodesCloneWithFacility
select nc.*,
case
whennc.NodeType='Start'thenn2.FacilityId
whennc.NodeType='End'thenn3.FacilityId
elsen.FacilityIdend FacilityId
into #NodesCloneWithFacility
from #NodesClone nc
left join #Nodes n onn.NodeId=nc.NodeId
/*Start node facility*/
left join #NodesPartition np onnp.PatientChronoId=2andnp.PatientId=nc.PatientId
left join #Nodes n2 onn2.NodeId=np.NodeId
/*End node facility*/
left join (
selectt.PatientId, NodeId
from (
select PatientId, max(PatientChronoId)-1 PatientChronoIdMaxMinus1
from #NodesPartition
group by PatientId
	) t
join #NodesPartition np onnp.PatientChronoId=t.PatientChronoIdMaxMinus1andnp.PatientId=t.PatientId
) np2 onnp2.PatientId=nc.PatientIdandnc.NodeType='End'
left join #Nodes n3 onn3.NodeId=np2.NodeId

-------------------- Analysis
--Example 1 - Edge level
--This is figure S3(A) in the manuscipt supplement.
selectn1.NodeType NodeType1, n2.NodeType NodeType2, AllowedQ, count(1) [Count]
from #Edges e
join #NodesClone n1 onn1.NodeId=e.NodeId1
join #NodesClone n2 onn2.NodeId=e.NodeId2
join #Adjudications a ona.NodeType1=n1.NodeTypeanda.Nodetype2=n2.NodeType
group byn1.NodeType, n2.NodeType, AllowedQ

--Example 2 - Edge level + NodeType1 statistics
--This is table S2 in the manuscript supplement
select t.*, t2.[Count] NodeType1Total, round(100*cast(t.[Count] asfloat)/t2.[Count], 0) EdgeType1TotalPct
from (
selectn1.NodeType NodeType1, n2.NodeType NodeType2, AllowedQ, count(1) [Count]
from #Edges e
join #NodesClone n1 onn1.NodeId=e.NodeId1
join #NodesClone n2 onn2.NodeId=e.NodeId2
join #Adjudications a ona.NodeType1=n1.NodeTypeanda.Nodetype2=n2.NodeType
group byn1.NodeType, n2.NodeType, AllowedQ
) t
left join (
selectn1.NodeType NodeType1, count(1) [Count]
from #Edges e
join #NodesClone n1 onn1.NodeId=e.NodeId1
join #NodesClone n2 onn2.NodeId=e.NodeId2
join #Adjudications a ona.NodeType1=n1.NodeTypeanda.Nodetype2=n2.NodeType
group byn1.NodeType
) t2 ont.NodeType1=t2.NodeType1


--Example 3 - (Example 1) @ Facility level
selectn1.NodeType NodeType1, n2.NodeType NodeType2, AllowedQ, n1.FacilityId NodeType1Facility, count(1) [Count]
from #Edges e
join #NodesCloneWithFacility n1 onn1.NodeId=e.NodeId1
join #NodesCloneWithFacility n2 onn2.NodeId=e.NodeId2
join #Adjudications a ona.NodeType1=n1.NodeTypeanda.Nodetype2=n2.NodeType
group byn1.NodeType, n2.NodeType, AllowedQ, n1.FacilityId

--Example 4 - (Example 2) @ Facility level
--This is table S3 in the manuscript supplement
select t.*, t2.[Count] NodeType1Total, round(100*cast(t.[Count] asfloat)/t2.[Count], 0) EdgeType1TotalPct
from (
selectn1.NodeType NodeType1, n2.NodeType NodeType2, AllowedQ, n1.FacilityId NodeType1Facility, count(1) [Count]
from #Edges e
join #NodesCloneWithFacility n1 onn1.NodeId=e.NodeId1
join #NodesCloneWithFacility n2 onn2.NodeId=e.NodeId2
join #Adjudications a ona.NodeType1=n1.NodeTypeanda.Nodetype2=n2.NodeType
group byn1.NodeType, n2.NodeType, AllowedQ, n1.FacilityId
) t
left join (
selectn1.NodeType NodeType1, n1.FacilityId NodeType1Facility, count(1) [Count]
from #Edges e
join #NodesCloneWithFacility n1 onn1.NodeId=e.NodeId1
join #NodesCloneWithFacility n2 onn2.NodeId=e.NodeId2
join #Adjudications a ona.NodeType1=n1.NodeTypeanda.Nodetype2=n2.NodeType
group byn1.NodeType, n1.FacilityId
) t2 ont.NodeType1=t2.NodeType1andt.NodeType1Facility=t2.NodeType1Facility