Postgres GROUP BY looking at dates ranges

Question

I have a table with the history of the "Code" value changes. Every month this table gets a new record with the new value of the "Code" for the specified month.

+----------+------------+------------+------+
| Employee | FromDate   |  ToDate    | Code |
+----------+------------+------------+------+
| Employee | 01/07/2016 | 31/07/2016 |    4 |
| Employee | 01/06/2016 | 30/06/2016 |    2 |
| Employee | 01/05/2016 | 31/05/2016 |    2 |
| Employee | 01/04/2016 | 30/04/2016 |    3 |
| Employee | 01/03/2016 | 31/03/2016 |    3 |
| Employee | 01/02/2016 | 29/02/2016 |    4 |
| Employee | 01/01/2016 | 31/01/2016 |    4 |
+----------+------------+------------+------+

I need to group by this data to get a new record every time "Code" changes and take the min value for the "From date" and the max value for the "To date". Data must be ordered descending by "FromDate". With my query I got this result:

+----------+------------+------------+------+
| Employee | FromDate   |  ToDate    | Code |
+----------+------------+------------+------+
| Employee | 01/05/2016 | 30/06/2016 |    2 |
| Employee | 01/03/2016 | 30/04/2016 |    3 |
| Employee | 01/01/2016 | 31/07/2016 |    4 |
+----------+------------+------------+------+

It works fine but if the same "Code" has more the one date range (see the 4 code in the first table) I got a single row per code. I would like get this result with the 4 code in 2 records because its period is not continuos but it's broke by others codes (3 and 2):

+----------+------------+------------+------+
| Employee | FromDate   |  ToDate    | Code |
+----------+------------+------------+------+
| Employee | 01/07/2016 | 31/07/2016 |    4 |
| Employee | 01/05/2016 | 30/06/2016 |    2 |
| Employee | 01/03/2016 | 30/04/2016 |    3 |
| Employee | 01/01/2016 | 29/02/2016 |    4 |
+----------+------------+------------+------+

I use this query:

SELECT
    d."Employee",
    MIN (d."FromDate") AS "FromDate",
    MAX (d."ToDate") AS "ToDate",
    d."Code"
FROM
    (
        SELECT
            "Employees"."FromDate",
            "Employees"."ToDate",
            "Employees"."Code",
            "Employees"."Employee"
        FROM
            schema_estelspa."Employees"
        ORDER BY
            "Employees"."FromDate" DESC
    ) d
GROUP BY
    d."Code",
    d."Employee"
ORDER BY
    (MIN(d."FromDate")) DESC

Is there any trick to get the result I desired?

Date format is: dd/MM/yyyy

Unrelated, but: you should _really_ try to avoid those dreaded quoted identifiers. They are much more trouble then they are worth it. — , Oct 21 '16 at 08:29
You get a single 4 code because you are grouping by code, try without `group by` — devasia2112, Oct 21 '16 at 09:09
@a_horse_with_no_name I'm using Entity Framework and it create the columns' name in Pascal case. So I need to quote them in Pg. — Darion Badlydone, Oct 21 '16 at 09:28
@B4NZ41 I need to group the dates (from and to) so I cannot remove the group by and neither group by employee — Darion Badlydone, Oct 21 '16 at 09:29
Possible duplicate of [group consecutive time intervals in sql](http://stackoverflow.com/questions/39286662/group-consecutive-time-intervals-in-sql) — Laurenz Albe, Oct 21 '16 at 10:32
It looks like [Packing Intervals](http://blogs.solidq.com/en/sqlserver/packing-intervals/). It is written for SQL Server, but latest versions of Postgres have same analytic functions as SQL Server. — Vladimir Baranov, Oct 21 '16 at 11:53

Bhavesh Ghodasara · Accepted Answer · 2016-10-21T09:41:31.237

Here you need to make date range and make from_date as one part of group by column. you also need to self join to achieve this result. I prepared following SQL in teradata. Please make necessary changes for your database(coalesc is used as if null expression, you can use nvl or case statement as well)

Query:

SELECT E.EMPLOYEE, E.CODE,COALESCE(ET1.FROMdATE,E.FROMDATE)FROM_DATE ,MAX(E.TODATE)TO_D
FROM EMP_TEST E
LEFT OUTER JOIN EMP_TEST ET1
ON E.EMPLOYEE=ET1.EMPLOYEE
AND E.CODE=ET1.CODE
AND E.FromDate=ET1.ToDate+1
GROUP BY 1,2,3
ORDER BY FROM_DATE

Output:

    Employee    Code    FROM_DATE   TO_D
1   Employee    4   1/1/2016    2/29/2016
2   Employee    2   5/1/2016    6/30/2016
3   Employee    4   7/1/2016    7/31/2016
4   Employee    3   3/1/2016    4/30/2016

joop · Answer 2 · 2016-10-21T11:36:39.063

Standard recursive solution for connecting-the-dots

in practice, half-open intervals (lower_limit <= X < upper_limit) are easier to work with
Recursion starts with any segment that does not have a lower neigbor
adjacent segments are glued to the right side, building longer chains
the final query suppresses partial results

Note: the code below does not deal with overlapping intervals.

        -- Table
CREATE TABLE ecode
        ( employee varchar NOT NULL
        , code INTEGER NOT NULL
        , fromdate DATE NOT NULL
        , uptodate DATE NOT NULL
        );
SET datestyle = 'DMY' ;

        -- Data
INSERT INTO ecode(employee, fromdate, uptodate, code) VALUES
 ('Employee','01/07/2016','31/07/2016', 4)
, ('Employee','01/06/2016','30/06/2016', 2)
, ('Employee','01/05/2016','31/05/2016', 2)
, ('Employee','01/04/2016','30/04/2016', 3)
, ('Employee','01/03/2016','31/03/2016', 3)
, ('Employee','01/02/2016','29/02/2016', 4)
, ('Employee','01/01/2016','31/01/2016', 4)
        ;

        -- Convert to half-open interval
UPDATE ecode SET uptodate = uptodate + '1 day'::interval;
-- SELECT * FROM ecode;

WITH RECURSIVE zzz AS (
        SELECT employee, code, fromdate, uptodate
        FROM ecode e0
        WHERE NOT EXISTS ( -- first one in series
                SELECT * FROM ecode nx
                WHERE nx.employee = e0.employee
                AND nx.code = e0.code
                AND nx.uptodate = e0.fromdate
                )
        UNION ALL -- append consecutive intervals
        SELECT e1.employee, e1.code, zzz.fromdate, e1.uptodate
        FROM ecode e1
        JOIN zzz ON zzz.employee = e1.employee
        AND zzz.code = e1.code
        AND zzz.uptodate = e1.fromdate
        )
SELECT * FROM zzz
        -- suppress the partial results
WHERE NOT EXISTS (SELECT * FROM ecode nx
                WHERE nx.employee = zzz.employee
                AND nx.code = zzz.code
                AND nx.fromdate = zzz.uptodate
                )
ORDER BY employee, code, fromdate
        ;

Result:

DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
SET
INSERT 0 7
UPDATE 7
 employee | code |  fromdate  |  uptodate  
----------+------+------------+------------
 Employee |    2 | 2016-05-01 | 2016-07-01
 Employee |    3 | 2016-03-01 | 2016-05-01
 Employee |    4 | 2016-01-01 | 2016-03-01
 Employee |    4 | 2016-07-01 | 2016-08-01
(4 rows)

I think you'll find [Packing Intervals](http://blogs.solidq.com/en/sqlserver/packing-intervals/) interesting. It shows how to deal with overlapping intervals. It is written for SQL Server, but Postgres has all the analytic functions used in the query. — Vladimir Baranov, Oct 21 '16 at 11:55
IMHO the best way to deal with overlapping intervals is edge-detection and summation over its results. — joop, Oct 21 '16 at 13:15

Postgres GROUP BY looking at dates ranges

2 Answers2

Result: