Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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 ~* '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, min(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 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' 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 ~* '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-11-25' group by e.name;
select distinct e.name, d.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-11-25' order by e.name, d.last_modified;


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, min(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-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 ~* '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 distinct e.name, 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-12-03' 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 ~* '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-12-03' group by e.name;

select distinct e.name, d.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-12-03' order by e.name, d.last_modified;