· 4 years ago · Apr 03, 2021, 03:38 AM
1SET ANSI_NULLS ON
2GO
3
4SET QUOTED_IDENTIFIER ON
5GO
6
7IF EXISTS (SELECT * FROM [dbo].sysobjects WHERE id = OBJECT_ID(N'owid.Extract_OwidCovidDataByDay') AND objectproperty(id,N'IsProcedure') = 1)
8BEGIN
9 PRINT 'Dropping Procedure owid.Extract_OwidCovidDataByDay'
10 DROP PROCEDURE owid.Extract_OwidCovidDataByDay
11END
12GO
13
14PRINT 'Creating Procedure owid.Extract_OwidCovidDataByDay'
15GO
16
17-- =============================================
18-- Author: Jillian Lenox
19-- Create date: 2021/03/26
20-- Description: Detailed COVID-19 Data by Day
21-- Updates:
22-- 2021/03/26 jlenox Created
23-- 2021/03/30 jlenox Updated fields and data types
24-- =============================================
25CREATE PROCEDURE owid.Extract_OwidCovidDataByDay (
26 @StartDate DATE = NULL,
27 @EndDate DATE = NULL
28)
29AS
30BEGIN
31 SET NOCOUNT ON;
32 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
33
34 -- Set input variables
35 IF (@StartDate IS NULL)
36 SELECT @StartDate = '2020-01-01' -- Custom date
37
38 IF (@EndDate IS NULL)
39 SELECT @EndDate = CAST(getdate() AS DATE) -- Current date
40
41 -- Drop existing table
42 IF OBJECT_ID (N'owid.OwidCovidDataByDay', N'U') IS NOT NULL
43 DROP TABLE owid.OwidCovidDataByDay
44
45 -- Main Query
46 -- ---------------------------------------------------------------------------
47 ;WITH cteTransactions AS (
48 SELECT
49 CAST([date] AS DATE) AS Date,
50 DATEPART(yy, date) AS DateYear,
51 DATEPART(mm, date) AS DateMonth,
52 --[iso_code] [nvarchar](max) NULL,
53 CAST([continent] AS varchar(25)) AS Continent,
54 CAST([location] AS varchar(50)) AS Location,
55 CAST(CAST([total_cases] AS decimal(20,0)) AS bigint) AS CumulativeCases,
56 CAST(CAST([new_cases] AS decimal(20,0)) AS bigint) AS NewCases,
57 CAST(CAST([new_cases_smoothed] AS decimal(20,0)) AS bigint) AS NewCasesSmoothed,
58 -- [total_deaths],
59 CAST(CAST([new_deaths] AS decimal(20,0)) AS bigint) AS NewDeaths,
60 CAST(CAST([new_deaths_smoothed] AS decimal(20,0)) AS bigint) AS NewDeathsSmoothed,
61 -- [total_cases_per_million],
62 -- [new_cases_per_million],
63 -- [new_cases_smoothed_per_million],
64 -- [total_deaths_per_million],
65 -- [new_deaths_per_million],
66 -- [new_deaths_smoothed_per_million],
67 -- [reproduction_rate],
68 -- [icu_patients],
69 -- [icu_patients_per_million],
70 CAST(CAST([hosp_patients] AS decimal(20,0)) AS bigint) AS HospitalizedPatients,
71 -- [hosp_patients_per_million],
72 -- [weekly_icu_admissions],
73 -- [weekly_icu_admissions_per_million],
74 -- [weekly_hosp_admissions],
75 -- [weekly_hosp_admissions_per_million],
76 -- [new_tests],
77 -- [total_tests]L,
78 -- [total_tests_per_thousand],
79 -- [new_tests_per_thousand],
80 CAST(CAST([new_tests_smoothed] AS decimal(20,0)) AS bigint) AS NewTestsSmoothed,
81 -- [new_tests_smoothed_per_thousand],
82 -- [positive_rate],
83 -- [tests_per_case],
84 -- [tests_units],
85 -- [total_vaccinations],
86 -- [people_vaccinated],
87 -- [people_fully_vaccinated],
88 -- [new_vaccinations],
89 CAST(CAST([new_vaccinations_smoothed] AS decimal(20,0)) AS bigint) AS NewVaccinationsSmoothed
90 -- [total_vaccinations_per_hundred],
91 -- [people_vaccinated_per_hundred],
92 -- [people_fully_vaccinated_per_hundred],
93 -- [new_vaccinations_smoothed_per_million],
94 -- [stringency_index],
95 -- [population],
96 -- [population_density],
97 -- [median_age],
98 -- [aged_65_older],
99 -- [aged_70_older],
100 -- [gdp_per_capita],
101 -- [extreme_poverty],
102 -- [cardiovasc_death_rate],
103 -- [diabetes_prevalence],
104 -- [female_smokers],
105 -- [male_smokers],
106 -- [handwashing_facilities],
107 -- [hospital_beds_per_thousand],
108 -- [life_expectancy],
109 -- [human_development_index]
110 FROM [owid].[OwidCovidData]
111 WHERE [date] < CAST(getdate() AS Date) -- Exclude current date because it's 0 or incomplete
112 )
113 ,cteAggregate AS (
114 SELECT [Location],
115 RANK() OVER ()
116 FROM cteTransactions
117
118 )
119 -- INSERT INTO [owid].[OwidCovidDataByDay] (
120 -- [Date]
121 -- ,[DateYear]
122 -- ,[DateMonth]
123 -- ,[Continent]
124 -- ,[Location]
125 -- ,[TotalCases]
126 -- ,[NewCases]
127 -- )
128 SELECT *
129 INTO owid.OwidCovidDataByDay
130 FROM cteTransactions
131 WHERE Continent IS NOT NULL -- Filter totals by contient (ie: World, Europe, North America)
132 --ORDER BY Date
133
134END
135
136/*
137 SELECT TOP 1000 * FROM owid.OwidCovidData ORDER BY date DESC
138 SELECT TOP 1000 * FROM owid.OwidCovidDataByDay
139 SELECT MAX(Date) FROM owid.OwidCovidDataByDay
140
141 -- Top 5 Locations by New cases
142 SELECT Location,
143 SUM(NewCases) AS SumNewCases,
144 RANK() OVER (ORDER BY Sum(NewCases) DESC) AS Rank
145 FROM owid.OwidCovidDataByDay
146 GROUP BY Location
147 ORDER BY SUM(NewCases) DESC
148
149 DROP TABLE owid.OwidCovidDataByDay
150*/