---------------------------------------
-- build-interventions.sql
-- Alex Szalay, 2020-03-30
---------------------------------------
use covidnyt
go

set nocount on
go

--====================================================================================================
-- JieYingWu data from JHU:  https://github.com/JieYingWu/COVID-19_US_County-level_Summaries
-- download from: https://github.com/JieYingWu/COVID-19_US_County-level_Summaries/tree/master/data
-- downloaded two files:
--		interventions.csv
-- need to replace NA with ''
-- also commented out 
--====================================================================================================
if OBJECT_ID(N'dbo.interventions_RAW', N'U') IS NOT NULL
    drop table dbo.interventions_RAW;
go
--
create table interventions_RAW (
	fips int NOT NULL,	
	[state] varchar(50) NOT NULL,
	county varchar(50) NOT NULL,
	stayhome int NULL,
	gather50 int NULL,
	gather500 int NULL,
	schools int NULL,
	dinein int NULL,
	entgym int NULL,
	fedGuide int NULL
)
go
--
bulk insert interventions_RAW FROM '\\dsp030\r$\covid-nyt\data\interventions-03-31.csv'
	WITH (FORMAT='CSV', FIRSTROW=2, FIELDTERMINATOR = ',', 
	ROWTERMINATOR = '\n');
go

------------------------------------------
-- create the Interventions table
------------------------------------------
if OBJECT_ID(N'dbo.Interventions', N'U') IS NOT NULL
    drop table dbo.Interventions;
go
--
create table Interventions (
	fips int NOT NULL,	
	[state] varchar(50) NOT NULL,
	county varchar(50) NULL,
	stayhome int NULL,
	gather50 int NULL,
	gather500 int NULL,
	schools int NULL,
	dinein int NULL,
	entgym int NULL,
	fedGuide int NULL
)
go
--
insert Interventions with (tablock)
select * from (

  select s.fips, 
	s.state, 
	replace(upper(substring(county,1,1))+ substring(county,2,100),' county','') county,
	stayhome, gather50, gather500, schools, dinein, entgym, fedGuide
  from Interventions_RAW i, states s
  where s.fips = floor(i.fips/1000)
    and i.fips % 1000 <>0

union all

  select s.fips, 
	s.state, 
	NULL county,
	stayhome, gather50, gather500, schools, dinein, entgym, fedGuide
  from Interventions_RAW i, States s
  where i.fips % 1000 = 0
    and i.fips=s.fips*1000
    and i.fips>0

) x
go

-------------------------------------------------------------
-- bring the day variable to the same base as the NYT data
-------------------------------------------------------------
declare @dd int = datediff(dd,'2020-01-21','2020-02-29');
update i
	set stayhome=stayhome+@dd, gather50 = gather50+@dd, 
		gather500 = gather500+@dd, 
		schools  =schools+@dd, dinein = dinein+@dd,
		entgym = entgym+@dd, fedguide = fedGuide+@dd
from Interventions i
go

if OBJECT_ID(N'dbo.interventions_RAW', N'U') IS NOT NULL
    drop table dbo.interventions_RAW;
go
--