Page Comparison

...

Baseline Crisis Data and new/updated Data on Crisis Onset

The SQL queries for the Rohingya Crisis MMR and BGD (in the order Baseline, Updates, Creates) are:

select b.name, a.last_modified, b.location from dbdatasets a, dbinfodatasets b, dbruns c where a.id = b.id and a.run_number = c.run_number and b.location like~~ ANY('{%mmr%,%bgd%}') and a.update_frequency != -1 and date(c.run_date) = '2017-08-24' and a.last_modified > '2016-02-24' order by a.last_modified;

with baseline as 
(select a.id, b.name from dbdatasets a, dbinfodatasets b, dbruns c where a.id = b.id and a.run_number = c.run_number and b.location like~~ ANY('{%mmr%,%bgd%}') and a.update_frequency != -1 and date(c.run_date) = '2017-08-24' and a.last_modified > '2016-02-24')
select e.name, max(d.last_modified) as last_modified from dbdatasets d, baseline e where d.id=e.id and d.last_modified > '2017-08-24' and d.last_modified < '2017-10-25' group by e.name;

select e.name, max(d.last_modified) as last_modified from dbdatasets d, dbinfodatasets e where e.location like~~ ANY('{%mmr%,%bgd%}') and d.update_frequency != -1 and d.id not in (select distinct a.id from dbdatasets a, dbinfodatasets b, dbruns c where a.id = b.id and 
a.run_number = c.run_number and b.location like~~ ANY('{%mmr%,%bgd%}') and c.run_date < '2017-08-25') and d.id=e.id and d.last_modified > '2017-08-24' and d.last_modified < '2017-10-25' group by e.name;

...

select b.name, a.last_modified, b.location from dbdatasets a, dbinfodatasets b, dbruns c where a.id = b.id and a.run_number = c.run_number and b.location like~~ ANY('{%aia%,%atg%,%bhs%,%brb%,%bes%,%vgb%,%cub%,%dma%,%dom%,%glp%,%hti%,%mtq%,%msr%,%pri%,%blm%,%kna%,%lca%,%maf%,%vct%,%sxm%,%tca%,%vir%}') and a.update_frequency != -1 and date(c.run_date) = '2017-09-02' and a.last_modified > '2016-03-02' order by a.last_modified;

with baseline as 
(select a.id, b.name from dbdatasets a, dbinfodatasets b, dbruns c where a.id = b.id and a.run_number = c.run_number and b.location like~~ ANY('{%aia%,%atg%,%bhs%,%brb%,%bes%,%vgb%,%cub%,%dma%,%dom%,%glp%,%hti%,%mtq%,%msr%,%pri%,%blm%,%kna%,%lca%,%maf%,%vct%,%sxm%,%tca%,%vir%}') and a.update_frequency != -1 and date(c.run_date) = '2017-09-02' and a.last_modified > '2016-03-02')
select e.name, max(d.last_modified) as last_modified from dbdatasets d, baseline e where d.id=e.id and d.last_modified > '2017-09-02' and d.last_modified < '2017-11-03' group by e.name;

select e.name, max(d.last_modified) as last_modified from dbdatasets d, dbinfodatasets e where e.location like~~ ANY('{%aia%,%atg%,%bhs%,%brb%,%bes%,%vgb%,%cub%,%dma%,%dom%,%glp%,%hti%,%mtq%,%msr%,%pri%,%blm%,%kna%,%lca%,%maf%,%vct%,%sxm%,%tca%,%vir%}') and d.update_frequency != -1 and d.id not in (select distinct a.id from dbdatasets a, dbinfodatasets b, dbruns c where a.id = b.id and 
a.run_number = c.run_number and b.location like~~ ANY('{%aia%,%atg%,%bhs%,%brb%,%bes%,%vgb%,%cub%,%dma%,%dom%,%glp%,%hti%,%mtq%,%msr%,%pri%,%blm%,%kna%,%lca%,%maf%,%vct%,%sxm%,%tca%,%vir%}') and c.run_date < '2017-09-03') and d.id=e.id and d.last_modified > '2017-09-02' and d.last_modified < '2017-11-03' group by e.name;