text: -- Creating combined records for jh_us_timeseries
  
  INSERT INTO combined
  (
    sourcecode,
    county,
    state,
    country,
    countrycode,
    date,
    confirmed
  )
  SELECT 
  '{{sourceCode}}' as sourcecode,
  county,
  state,
  country,
  'USA' as countrycode,
  date,
  CASE 
  	WHEN ct = 0 THEN null
  	ELSE ct
  END as confirmed
  FROM jh_us_timeseries ts
  WHERE type = 'C'
  AND date > '{{lastUpdate}}';
  
  -- USA National rollup entries loaded from jh_timeseries (Global)
  
  -- Create State rollup entries
  INSERT INTO combined
  (
    sourcecode,
    state,
    country,
    countrycode,
    date,
    confirmed
  )
  SELECT 
  '{{sourceCode}}' as sourcecode,
  state,
  country,
  'USA' as countrycode,
  date,
  SUM(ct) as confirmed
  FROM jh_us_timeseries ts
  WHERE type = 'C'
  AND date > '{{lastUpdate}}'
  GROUP BY sourcecode, state, country, countrycode, date;
  
  
  -- Update statecodes
  UPDATE combined
  SET statecode = state.ansi
  FROM state
  WHERE combined.state = state.name
  AND combined.statecode is null;
  
  -- Confirmed updates
  
  /*
  UPDATE combined SET countrycode = 'USA' WHERE country = 'United States' AND countrycode is null;
  
  SELECT * FROM combined WHERE (state is null OR state = '') AND countrycode = 'USA';
  SELECT * FROM combined WHERE state is not null AND county is null AND countrycode = 'USA';
  
  SELECT count(date) as ct FROM combined WHERE countrycode = 'USA';
  */
  
  -- Death ct updates
  
  -- State rollups
  UPDATE combined c1
  SET death = v1.death
  FROM( SELECT SUM(ct) as death, date, state,country
   	FROM jh_us_timeseries jh
  	WHERE type = 'D'
      GROUP BY date, state,country)v1
  WHERE c1.state = v1.state
  AND c1.date = v1.date
  AND c1.country = v1.country
  AND c1.county is null
  AND c1.death is null; 
  
  
  -- county Records
  UPDATE combined c1
  SET death = v1.ct
  FROM( SELECT * FROM jh_us_timeseries
  WHERE type = 'D') AS v1
  WHERE (c1.state = v1.state)
  AND c1.country = v1.country
  AND c1.county = v1.county
  AND c1.date = v1.date
  AND c1.date > '{{lastUpdate}}'
  AND c1.death is null;
  
  