· 5 years ago · Mar 26, 2020, 04:32 PM
1DROP TABLE IF EXISTS ##Curves;
2DROP TABLE IF EXISTS ##SourceRow;
3
4DECLARE @Loop int,
5 @LoopMax int,
6 @SQL nvarchar(max),
7 @LabelToInvestigate nvarchar(200)
8
9SET @LabelToInvestigate = 'Washtenaw, Michigan, US'
10
11CREATE TABLE ##SourceRow
12(
13Source_Label nvarchar(200),
14Source_Province_State nvarchar(200),
15Source_Confirmed nvarchar(50),
16Source_Last_Update date
17)
18
19CREATE TABLE ##Curves
20(
21 AnchorDay date,
22 Label nvarchar(200),
23 Day0 nvarchar(50)
24);
25
26INSERT INTO ##SourceRow
27SELECT Label 'Source_Label',
28 Province_State 'Source_Province_State',
29 Confirmed 'Source_Confirmed',
30 Last_Update 'Source_Last_Update'
31 FROM JH_HX
32 WHERE NOT EXISTS
33 ( SELECT 1
34 FROM JH_HX JH_Inner
35 WHERE JH_HX.Label = JH_Inner.Label
36 AND JH_HX.Last_Update < JH_Inner.Last_Update )
37 AND Label = @LabelToInvestigate;
38
39Select * from ##SourceRow;
40
41WITH SimilarPoints AS
42(
43select JH_HX.Label,
44 JH_HX.Last_Update AnchorDate,
45 ##SourceRow.Source_Confirmed,
46 JH_HX.Confirmed,
47 ABS( ##SourceRow.Source_Confirmed -JH_HX.Confirmed ) as CaseDiff,
48 ROW_NUMBER() OVER (ORDER BY ABS( ##SourceRow.Source_Confirmed -JH_HX.Confirmed ) ) as SimilarityRank
49 FROM ##SourceRow
50 OUTER APPLY JH_HX
51 WHERE NOT EXISTS
52 (
53 SELECT 1
54 FROM JH_HX JH_Inner
55 WHERE JH_HX.Label = JH_Inner.Label
56 AND ( ( ABS( ##SourceRow.Source_Confirmed - JH_HX.Confirmed ) > ABS( ##SourceRow.Source_Confirmed - JH_Inner.Confirmed ) )
57 OR ( ABS( ##SourceRow.Source_Confirmed - JH_HX.Confirmed ) = ABS( ##SourceRow.Source_Confirmed - JH_Inner.Confirmed ) AND JH_HX.Last_Update > JH_Inner.Last_Update ) )
58 )
59 AND DATEDIFF(day, JH_HX.Last_Update, (SELECT MAX(Last_Update) FROM JH_HX JH_INNER WHERE JH_INNER.Label = JH_HX.Label ) ) > 5
60 AND JH_HX.Label <> ##SourceRow.Source_Label
61 AND Country_Region NOT IN ('Mainland China', 'China', 'Cruise Ship', 'Others')
62 AND ( Province_State NOT IN ( ##SourceRow.Source_Province_State, 'From Diamond Princess', 'Unassigned Location (From Diamond Princess)' ) OR Province_State IS NULL )
63)
64
65INSERT INTO ##Curves
66( AnchorDay,
67 Label,
68 Day0
69)
70
71SELECT AnchorDate as AnchorDay,
72 Label,
73 Confirmed as Day0
74FROM SimilarPoints
75WHERE SimilarityRank <= 6
76
77SELECT @LoopMax = MAX (DateDiff( day, AnchorDay, DATEADD( day, -1, GETDATE() ) ) )
78FROM ##Curves
79
80SET @Loop = 0
81
82SET @SQL = 'ALTER TABLE ##Curves'
83
84WHILE @Loop < @LoopMax
85BEGIN
86 SET @Loop = @Loop + 1
87 SET @SQL = @SQL + '
88 ' + CASE WHEN @Loop = 1 THEN 'ADD ' ELSE '' END + 'Day' + cast(@loop as nvarchar(4)) +' nvarchar(50)' + CASE WHEN @Loop = @LoopMax THEN '' ELSE ',' END
89END
90
91execute sp_executesql @SQL;
92
93SET @Loop = 0
94
95WHILE @Loop < @LoopMax
96BEGIN
97SET @Loop = @Loop + 1
98SET @SQL = '
99UPDATE ##Curves
100SET Day' + CAST(@Loop as nvarchar(4)) + ' = Confirmed
101 FROM JH_HX
102 WHERE Last_Update = DATEADD(day, ' + CAST(@Loop as nvarchar(4)) + ', ##Curves.AnchorDay )
103 AND ##Curves.Label = JH_HX.Label'
104
105exec sp_executesql @SQL
106END
107
108SET @Loop = 0
109SET @SQL = 'SELECT Label,
110AnchorDay,
111COALESCE(Day0, ''#N/A'') as Day0,'
112
113WHILE @Loop < @LoopMax
114BEGIN
115 SET @Loop = @Loop + 1
116 SET @SQL = @SQL + '
117 COALESCE(Day'+ CAST(@Loop as nvarchar(4)) + ', ''#N/A'') as Day' + CAST(@Loop as nvarchar(4)) + CASE WHEN @Loop < @LoopMax THEN ',' ELSE '' END
118END
119
120SET @SQL = @SQL + '
121 FROM ##Curves'
122
123PRINT @SQL
124exec sp_executesql @SQL