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, Number of Updates, List of Updates, Number of Creates, List of 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~* '%mmr%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~* '%mmr%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, maxmin(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-11-25' group by e.name;

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 ~* 'mmr|bgd' and a.update_frequency != -1 and date(c.run_date) = '2017-08-24' and a.last_modified > '2016-02-24')
select distinct e.name, d.last_modified from dbdatasets d, max baseline e where d.id = e.id and d.last_modified > '2017-08-24' and d.last_modified < '2017-11-25' order by e.name, d.last_modified;

select e.name, min(d.last_modified) as last_modified from dbdatasets d, dbinfodatasets e where e.location like~* '%mmr%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 '%mmr% ~* '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-11-25' group by e.name;

select distinct e.name, d.last_modified from dbdatasets d, dbinfodatasets e where e.location ~* 'mmr|bgd' and ad.update_frequency != -1 and date(c.run_date) = '2017-08-24' and a.last_modified > '2016-02-24 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 ~* '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-11-25' grouporder by e.name, d.last_modified;

The SQL queries for the the Hurricane Irma Crisis () and Maria Crises combined with affected countries taken from https://reliefweb.int/disaster/tc-2017-000125-dom and https://reliefweb.int/disaster/tc-2017-000136-atg (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 '%%~* '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 '%%~* '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, maxmin(d.last_modified) as last_modified from dbdatasets d, baseline e where d.id = e.id and d.last_modified > '2017-09-0302' group by e.name;
select e.name, max(and d.last_modified) as last_modified from dbdatasets d, dbinfodatasets e where e.location like '%%' and d.update_frequency != -1 and d.id not in  < '2017-12-03' group by e.name;

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 '%%~* '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') and
select distinct e.name, d.last_modified from dbdatasets d, baseline e where d.id = e.id and d.last_modified > '2017-09-02' and d.last_modified < '2017-12-03' grouporder by e.name;

...

, d.last_modified;

select be.name, amin(d.last_modified, b.location) as last_modified from dbdatasets ad, dbinfodatasets b, dbruns ce where a.id = b.id and a.run_number = c.run_number and be.location like '%%~* 'aia|atg|bhs|brb|bes|vgb|cub|dma|dom|glp|hti|mtq|msr|pri|blm|kna|lca|maf|vct|sxm|tca|vir' and ad.update_frequency != -1 and date(c.run_date) = '2017-09-15' and a.last_modified > '2016-03-15' order by a.last_modified;
with baseline as 
(selectd.id not in (select distinct 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 '%%' and a.update_frequency != -1 and date(~* '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-1503') and ad.last_modified > '2016-03-15')
selectid = e.name, max(id and d.last_modified) as last_modified from dbdatasets d, baseline e where d.id=e.id > '2017-09-02' and d.last_modified >< '2017-0912-1603' group by e.name;

select distinct e.name, max(d.last_modified) as last_modified from dbdatasets d, dbinfodatasets e where e.location like '%%~* '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 '%%' and a.update_frequency != -1 and date(~* '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-15'03') and d.id = e.id and ad.last_modified > '20162017-0309-15') and d.id=e.id02' and d.last_modified >< '2017-0912-1603' grouporder by e.name, d.last_modified;