...
| Crisis | Day0 | Countries | ISO3 | Baseline | Updates | Creates |
|---|---|---|---|---|---|---|
| Rohingya | Myanmar, Bangladesh | MMR, BGD | 98 | 18 | 2835 | |
| Irma/Maria | Anguilla, Antigua and Barbuda, Barbados, Bahamas, Bonaire, Saint Eustatius and Saba (The Netherlands), British Virgin Islands, Cuba, Dominica, Dominican Republic, Guadeloupe (France), Haiti, Martinique (France), Montserrat, Puerto Rico (The United States of America), Saint Barthélemy (France), Saint Kitts and Nevis, Saint Lucia, Saint Martin (France), Saint Vincent and the Grenadines, Sint Maarten (The Netherlands), Turks and Caicos Islands, United States Virgin Islands | AIA, ATG, BHS, BRB, BES, VGB, CUB, DMA, DOM, GLP, HTI, MTQ, MSR, PRI, BLM, KNA, LCA, MAF, VCT, SXM, TCA, VIR | 196 | 71 | 88114 |
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 ~* '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 ~* '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-1011-25' group by e.name;
select e.name, max(d.last_modified) as last_modified from dbdatasets d, dbinfodatasets e where e.location ~* '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 ~* '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-1011-25' group by e.name;
The SQL queries for the the Hurricane Irma 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):
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 ~* '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 ~* '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-1112-03' group by e.name;
select e.name, max(d.last_modified) as last_modified from dbdatasets d, dbinfodatasets e where e.location ~* '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 ~* '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-1112-03' group by e.name;