...
| Crisis | Day0 | Country | ISO3 | Baseline | Updates | Creates | ||
|---|---|---|---|---|---|---|---|---|
| Rohingya | Myanmar | MMR | 54 | 97 | 1811 | |||
| Rohingya | Bangladesh | BGD | 76 | 13 | 3811 | 18 | ||
| Irma/Maria | Anguilla | AIA | 8 | 1 | 11 | |||
| Irma/Maria | Antigua and Barbuda | ATG | 26 | 7 | 14 | |||
| Irma/Maria | Bahamas | BHS | 43 | 7 | 7 | |||
| Irma/Maria | Barbados | BRB | 35 | 5 | 6 | |||
| Irma/Maria | Bonaire, Saint Eustatius and Saba (The Netherlands) | BES | 0 | 0 | 0 | |||
| Irma/Maria | British Virgin Islands | VGB | 11 | 5 | 9 | |||
| Irma/Maria | Cuba | CUB | 55 | 7 | 7 | |||
| Irma/Maria | Dominica | DMA | 17 | 3 | 21 | |||
| Irma/Maria | Dominican Republic | DOM | 47 | 8 | 14 | |||
| Irma/Maria | Guadeloupe (France) | GLP | ||||||
| Irma/Maria | Haiti | HTI | ||||||
| Irma/Maria | Martinique (France) | MTQ | ||||||
| Irma/Maria | Montserrat | MSR | ||||||
| Irma/Maria | Puerto Rico (The United States of America) | PRI | ||||||
| Irma/Maria | Saint Barthélemy (France) | BLM | ||||||
| Irma/Maria | Saint Kitts and Nevis | KNA | ||||||
| Irma/Maria | Saint Lucia | LCA | ||||||
| Irma/Maria | Saint Martin (France) | MAF | ||||||
| Irma/Maria | Saint Vincent and the Grenadines | VCT | ||||||
| Irma/Maria | Sint Maarten (The Netherlands) | SXM | ||||||
| Irma/Maria | Turks and Caicos Islands | TCA | ||||||
| Irma/Maria | United States Virgin Islands | VIR |
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 '%mmr%' 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%' 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 '%mmr%' and d.update_frequency != -1 and d.id not in (select 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%' and a.update_frequency != -1 and date(c.run_date) =< '2017-08-2425') and ad.id=e.id and d.last_modified > '20162017-0208-24') and d.id=e.id and d.last_modified >< '2017-0810-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 like '%%%aia%' 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%' 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 '%%%aia%' and d.update_frequency != -1 and d.id not in (select 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 '%%%aia%' and a.update_frequency != -1 and date(c.run_date) =< '2017-09-0203') and ad.id=e.id and d.last_modified > '20162017-0309-02') and d.id=e.id and d.last_modified >< '2017-0911-03' group by e.name;