1

I have a table of country-periods. In some cases, certain country attributes (e.g. the capital) changes on a date within a time period. Here I would like to split the country-period into two new periods, one before and one after this change.

Example:

Country |  start_date   |  end_date   | event_date 
   A    |  1960-01-01   | 1999-12-31  | 1994-07-20
   B    |  1926-01-01   | 1995-12-31  | NULL

Desired output: 

Country |  start_date   |  end_date   | event_date 
   A    |  1960-01-01   | 1994-07-19  | 1994-07-20
   A    |  1994-07-20   | 1999-12-31  | 1994-07-20
   B    |  1926-01-01   | 1995-12-31  | NULL

I considered starting off with generate_series along these lines:

SELECT country, min(p1) as sdate1, max(p1) as sdate2,
min(p2) as sdate2, min(p2) as edate2 
  FROM 
   (SELECT country, 
    generate_series(start_date, (event_date-interval '1 day'), interval '1 day')::date as p1,
    generate_series(event_date, end_date, interval '1 day')::date as p2
   FROM table)t 
GROUP BY country

But these seems way to inefficient and messy. Unfortunately I don't have any experience when it comes to writing functions. Any ideas on how I can solve this?

guyus
  • 25
  • 4

1 Answers1

1

You can do UNION instead. This way you don't generate unnecessary rows

SELECT country, start_date, 
       CASE WHEN event_date BETWEEN start_date AND end_date 
            THEN event_date - 1 
            ELSE end_date
       END AS end_date, event_date
  FROM table1
 UNION ALL
SELECT country, event_date, end_date, event_date
  FROM table1
 WHERE event_date BETWEEN start_date AND end_date
 ORDER BY country, start_date, end_date, event_date

Here is a SQLFiddle demo

Output:

| country | start_date |   end_date | event_date |
|---------|------------|------------|------------|
|       A | 1960-01-01 | 1994-07-19 | 1994-07-20 |
|       A | 1994-07-20 | 1999-12-31 | 1994-07-20 |
|       B | 1926-01-01 | 1995-12-31 |     (null) |
peterm
  • 91,357
  • 15
  • 148
  • 157
  • Thanks, this was really helpful! However, I ran into problems in cases where a country experiences several "events" within the same period. Here I would need to generate multiple periods: one before the first event, one between the first and the second event, and so on. This is does not work with the suggested query. See the adjusted [SQL Fiddle](http://sqlfiddle.com/#!17/25d49/1) demo. I spent a few hours trying to resolve this but couldn't find a solution yet. Do you have any idea on how to address this issue? – guyus Jul 28 '17 at 14:35