Darion Badlydone Darion Badlydone - 1 month ago 14
SQL Question

Postgres GROUP BY looking at dates ranges

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

Answer

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