PostgreSQL – Generate Continuous Series for Multiple Categories

join;postgresql

I have a table that looks like this:

update_date  start_date   end_date   category_1 category_2    value
2013-03-04   2013-05-11  2013-06-08     a1           b1        23
2013-02-19   2013-03-16  2013-09-21     a2           b2       112
..

Now i want to transform the above table to this format:

date   category_1   category_2   value   update_date

For each pair of (category_1, category_2) I want to have a continuous series such as date is between start_date and end_date for each pair of (category_1, category_2) and keeping the respective value and update_date.

For example, for (a1, b1) I would have:

date       category_1  category_2   value   update_date
2013-05-11    a1          b1          23     2013-03-04
.             a1          b1          23     2013-03-04
.             a1          b1          23     2013-03-04
2013-06-08    a1          b1          23     2013-03-04

and this table would continue for each pair of (category_1, category_2).

I reckon there'd be duplicates of values in date column but i'm OK with that as long as there are no duplicates of (category_1, category_2) pair.

Anybody knows how to solve this?

Thank you in advance.

Best Answer

You can use generate_series() function:

select
    d.d::date as date, 
    t.update_date,  
    t.category_1, 
    t.category_2,
    t.value
from 
    t, 
    generate_series(start_date, end_date, interval '1 day') as d(d) ;

It works basically by taking each row from t and producing a set of rows, one for each day from start_date up to (and including) end_date.

Test at dbfiddle.uk

Related Solutions

MySQL – Query to Find Records Between Two Date Ranges

Your query has a logical flaw: it tries to find people who are NOT in the list of people who DO have coaching but only if the coaching is NOT in the target window.

Instead, join the employee and coaching table on employee_id, using all rows in employee but only using rows in coaching where the stop_date is later than or equal to the desired start date (coaching ends after the window starts) and the the start_date is less than or equal to the desired stop date (coaching starts before the window ends). Then exclude the employees with one or more matching rows in coaching.

SELECT e.id AS employee_id
  FROM employee e
  LEFT JOIN coaching c ON c.employee_id = e.id
                      AND c.end_date >= '2014-12-26'
                      AND c.start_date <= '2015-01-30'
 WHERE c.employee_id IS NULL;

Live demo: http://sqlfiddle.com/#!9/79b39/4

Mysql – How to display all the dates between multiple two dates in a table

The easiest way is to have a calendar table, defined in the following way:

CREATE TABLE calendar
(
    a_day date PRIMARY KEY
) ;

... and filled with all the relevant dates (i.e.: all days from 1990-1-1 to 2100-12-31). For the sake of simplicity, we will fill it only with year 2013:

INSERT INTO 
     calendar (a_day)
VALUES
    ('2013-01-01'),
    ('2013-01-02'),
    ('2013-01-03'),
    ('2013-01-04'),
    ('2013-01-05'),
    -- everything up to
    ('2013-12-31') ;

At this point, you can just have a JOIN with the two tables; with the join condition not being an equality, but a range condition:

SELECT
     t.id, c.a_day
FROM
     t
     JOIN calendar c ON c.a_day BETWEEN t.start_date AND t.end_date 
ORDER BY
     t.id, c.a_day ;

... and get

id | a_day     
-: | :---------
 1 | 2013-01-14
 1 | 2013-01-15
 1 | 2013-01-16
 1 | 2013-01-17
 1 | 2013-01-18
 2 | 2013-02-01
 2 | 2013-02-02
 2 | 2013-02-03
 2 | 2013-02-04

You can see all the setup at dbfiddle here

Best Answer

Related Solutions

MySQL – Query to Find Records Between Two Date Ranges

Mysql – How to display all the dates between multiple two dates in a table

Related Question