SQL query, create groups by dates

Question

This is my initial table, (the dates are in DD/MM/YY format)

  ID DAY       TYPE_ID TYPE  NUM START_DATE END_DATE 
---- --------- ------- ---- ---- ---------  ---------
4241 15/09/15        2    1   66  01/01/00   31/12/99  
4241 16/09/15        2    1   66  01/01/00   31/12/99  
4241 17/09/15        9    1   59  17/09/15   18/09/15  
4241 18/09/15        9    1   59  17/09/15   18/09/15  
4241 19/09/15        2    1   66  01/01/00   31/12/99  
4241 20/09/15        2    1   66  01/01/00   31/12/99  

4241 15/09/15        3    2   63  01/01/00   31/12/99  
4241 16/09/15        8    2  159  16/09/15   17/09/15  
4241 17/09/15        8    2  159  16/09/15   17/09/15 
4241 18/09/15        3    2   63  01/01/00   31/12/99   
4241 19/09/15        3    2   63  01/01/00   31/12/99  
4241 20/09/15        3    2   63  01/01/00   31/12/99  

2134 15/09/15        2    1   66  01/01/00   31/12/99  
2134 16/09/15        2    1   66  01/01/00   31/12/99  
2134 17/09/15        9    1   59  17/09/15   18/09/15  
2134 18/09/15        9    1   59  17/09/15   18/09/15  
2134 19/09/15        2    1   66  01/01/00   31/12/99  
2134 20/09/15        2    1   66  01/01/00   31/12/99  

2134 15/09/15        3    2   63  01/01/00   31/12/99  
2134 16/09/15        8    2  159  16/09/15   17/09/15  
2134 17/09/15        8    2  159  16/09/15   17/09/15 
2134 18/09/15        3    2   63  01/01/00   31/12/99   
2134 19/09/15        3    2   63  01/01/00   31/12/99  
2134 20/09/15        3    2   63  01/01/00   31/12/99

And I've to create groups with initial DAY and end DAY for the same ID, and TYPE.

I don't want to group by day, I need to create a group every time my TYPE_ID changes, based on the initial order (ID, TYPE, DAY ASC)

This is the result that I want to achieve:

  ID DAY_INI    DAY_END        TYPE_ID TYPE  NUM START_DATE END_DATE   
---- ---------  ---------      ------- ---- ---- ---------  ---------
4241 15/09/15   16/09/15             2    1   66  01/01/00   31/12/99  
4241 17/09/15   18/09/15             9    1   59  17/09/15   18/09/15  
4241 19/09/15   20/09/15             2    1   66  01/01/00   31/12/99  

4241 15/09/15   15/09/15             3    2   63  01/01/00   31/12/99  
4241 16/09/15   17/09/15             8    2  159  16/09/15   17/09/15  
4241 18/09/15   20/09/15             3    2   63  01/01/00   31/12/99   

2134 15/09/15   16/09/15             2    1   66  01/01/00   31/12/99  
2134 17/09/15   18/09/15             9    1   59  17/09/15   18/09/15  
2134 19/09/15   20/09/15             2    1   66  01/01/00   31/12/99  

2134 15/09/15   15/09/15             3    2   63  01/01/00   31/12/99  
2134 16/09/15   17/09/15             8    2  159  16/09/15   17/09/15  
2134 18/09/15   20/09/15             3    2   63  01/01/00   31/12/99

Could you please give any clue about how to do it??, thanks!

`Select ID, DAY_INI, DAY_END, TYPE_ID, TYPE, NUM, START_DATE, END_DATE from table group by ID, Type` didn't work for u? — Amnesh Goel, Sep 21 '15 at 08:25
Could you please specify which order by clause are you using to show you initial table resultset? — Simimmo, Sep 21 '15 at 08:39
@gabuh try this `Select ID, DAY_INI, DAY_END, TYPE_ID, TYPE, NUM, START_DATE, END_DATE from table group by ID, Type, Day` — Amnesh Goel, Sep 21 '15 at 08:42
@Amnesh Goel, that is not going to work, I don't want to group by day, I want to create a group every time my TYPE_ID changes, based on the initial order. I will update my question to clarify this. — gabuh, Sep 21 '15 at 08:46
@gabuh Your output order saying that output is order by ID, type and then day.. — Amnesh Goel, Sep 21 '15 at 08:47
@gabuh Probably you would need to use `Group By` and `order by` both.. `Select from table group by ID, Type order by Day_INI, Day_END` — Amnesh Goel, Sep 21 '15 at 08:51
Want to add 1 more point. `I want to create a group every time my TYPE_ID changes` -> this will never work unless you have a unique identifier for a row. What is the garuntee that your source will hold the data in same order as you have given? Believe me I ran into deep trouble once as I assumed that the data will be in same order. — Utsav, Sep 21 '15 at 08:52
Perhaps this question here could help- http://stackoverflow.com/questions/13037749/crystal-reports-need-to-group-by-derived-date-range — Rachcha, Sep 21 '15 at 09:19
Thanks @Rachcha, I managed to get the result with the help of that question. — gabuh, Sep 21 '15 at 09:43

score 0 · Answer 1 · answered Sep 21 '15 at 08:55

0

Add an enumeration to the original data set (using Row_Number or rownum). Add the MIN(Enumeration) for each group. Then sort the groups by the enumeration.

answered Sep 21 '15 at 08:55

Amir Pelled

591
4
13

score 0 · Accepted Answer · answered Sep 21 '15 at 09:32

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TEST ( ID, DAY, TYPE_ID, TYPE, NUM, START_DATE, END_DATE ) AS
          SELECT 4241, DATE '2015-09-15', 2, 1, 66, DATE '2000-01-01', DATE '1999-12-31' FROM DUAL  
UNION ALL SELECT 4241, DATE '2015-09-16', 2, 1, 66, DATE '2000-01-01', DATE '1999-12-31' FROM DUAL  
UNION ALL SELECT 4241, DATE '2015-09-17', 9, 1, 59, DATE '2015-09-17', DATE '2015-09-18' FROM DUAL  
UNION ALL SELECT 4241, DATE '2015-09-18', 9, 1, 59, DATE '2015-09-17', DATE '2015-09-18' FROM DUAL  
UNION ALL SELECT 4241, DATE '2015-09-19', 2, 1, 66, DATE '2000-01-01', DATE '1999-12-31' FROM DUAL  
UNION ALL SELECT 4241, DATE '2015-09-20', 2, 1, 66, DATE '2000-01-01', DATE '1999-12-31' FROM DUAL  
UNION ALL SELECT 4241, DATE '2015-09-15', 3, 2, 63, DATE '2000-01-01', DATE '1999-12-31' FROM DUAL  
UNION ALL SELECT 4241, DATE '2015-09-16', 8, 2, 159, DATE '2015-09-16', DATE '2015-09-17' FROM DUAL  
UNION ALL SELECT 4241, DATE '2015-09-17', 8, 2, 159, DATE '2015-09-16', DATE '2015-09-17' FROM DUAL 
UNION ALL SELECT 4241, DATE '2015-09-18', 3, 2, 63, DATE '2000-01-01', DATE '1999-12-31' FROM DUAL   
UNION ALL SELECT 4241, DATE '2015-09-19', 3, 2, 63, DATE '2000-01-01', DATE '1999-12-31' FROM DUAL  
UNION ALL SELECT 4241, DATE '2015-09-20', 3, 2, 63, DATE '2000-01-01', DATE '1999-12-31' FROM DUAL  
UNION ALL SELECT 2134, DATE '2015-09-15', 2, 1, 66, DATE '2000-01-01', DATE '1999-12-31' FROM DUAL  
UNION ALL SELECT 2134, DATE '2015-09-16', 2, 1, 66, DATE '2000-01-01', DATE '1999-12-31' FROM DUAL  
UNION ALL SELECT 2134, DATE '2015-09-17', 9, 1, 59, DATE '2015-09-17', DATE '2015-09-18' FROM DUAL  
UNION ALL SELECT 2134, DATE '2015-09-18', 9, 1, 59, DATE '2015-09-17', DATE '2015-09-18' FROM DUAL  
UNION ALL SELECT 2134, DATE '2015-09-19', 2, 1, 66, DATE '2000-01-01', DATE '1999-12-31' FROM DUAL  
UNION ALL SELECT 2134, DATE '2015-09-20', 2, 1, 66, DATE '2000-01-01', DATE '1999-12-31' FROM DUAL  
UNION ALL SELECT 2134, DATE '2015-09-15', 3, 2, 63, DATE '2000-01-01', DATE '1999-12-31' FROM DUAL  
UNION ALL SELECT 2134, DATE '2015-09-16', 8, 2, 159, DATE '2015-09-16', DATE '2015-09-17' FROM DUAL  
UNION ALL SELECT 2134, DATE '2015-09-17', 8, 2, 159, DATE '2015-09-16', DATE '2015-09-17' FROM DUAL 
UNION ALL SELECT 2134, DATE '2015-09-18', 3, 2, 63, DATE '2000-01-01', DATE '1999-12-31' FROM DUAL   
UNION ALL SELECT 2134, DATE '2015-09-19', 3, 2, 63, DATE '2000-01-01', DATE '1999-12-31' FROM DUAL  
UNION ALL SELECT 2134, DATE '2015-09-20', 3, 2, 63, DATE '2000-01-01', DATE '1999-12-31' FROM DUAL

Query 1:

WITH group_changes AS (
  SELECT t.*,
         CASE TYPE_ID WHEN LAG( TYPE_ID ) OVER ( PARTITION BY ID, TYPE ORDER BY DAY ) THEN 0 ELSE 1 END AS HAS_CHANGED_GROUP
  FROM   TEST t
),
groups AS (
  SELECT ID, DAY, TYPE_ID, TYPE, NUM, START_DATE, END_DATE,
         SUM( HAS_CHANGED_GROUP ) OVER ( PARTITION BY ID, TYPE ORDER BY DAY ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS GRP
  FROM   group_changes
)
SELECT ID,
       MIN( DAY ) AS DAY_INI,
       MAX( DAY ) AS DAY_END,
       MIN( TYPE_ID ) AS TYPE_ID,
       TYPE,
       MIN( NUM ) AS NUM,
       MIN( START_DATE ) AS START_DATE,
       MIN( END_DATE ) AS END_DATE
FROM   groups
GROUP BY ID, TYPE, GRP

Results:

|   ID |                     DAY_INI |                     DAY_END | TYPE_ID | TYPE | NUM |                  START_DATE |                    END_DATE |
|------|-----------------------------|-----------------------------|---------|------|-----|-----------------------------|-----------------------------|
| 4241 | September, 17 2015 00:00:00 | September, 18 2015 00:00:00 |       9 |    1 |  59 | September, 17 2015 00:00:00 | September, 18 2015 00:00:00 |
| 2134 | September, 15 2015 00:00:00 | September, 15 2015 00:00:00 |       3 |    2 |  63 |   January, 01 2000 00:00:00 |  December, 31 1999 00:00:00 |
| 2134 | September, 18 2015 00:00:00 | September, 20 2015 00:00:00 |       3 |    2 |  63 |   January, 01 2000 00:00:00 |  December, 31 1999 00:00:00 |
| 4241 | September, 15 2015 00:00:00 | September, 16 2015 00:00:00 |       2 |    1 |  66 |   January, 01 2000 00:00:00 |  December, 31 1999 00:00:00 |
| 4241 | September, 19 2015 00:00:00 | September, 20 2015 00:00:00 |       2 |    1 |  66 |   January, 01 2000 00:00:00 |  December, 31 1999 00:00:00 |
| 4241 | September, 15 2015 00:00:00 | September, 15 2015 00:00:00 |       3 |    2 |  63 |   January, 01 2000 00:00:00 |  December, 31 1999 00:00:00 |
| 4241 | September, 16 2015 00:00:00 | September, 17 2015 00:00:00 |       8 |    2 | 159 | September, 16 2015 00:00:00 | September, 17 2015 00:00:00 |
| 2134 | September, 17 2015 00:00:00 | September, 18 2015 00:00:00 |       9 |    1 |  59 | September, 17 2015 00:00:00 | September, 18 2015 00:00:00 |
| 2134 | September, 15 2015 00:00:00 | September, 16 2015 00:00:00 |       2 |    1 |  66 |   January, 01 2000 00:00:00 |  December, 31 1999 00:00:00 |
| 2134 | September, 19 2015 00:00:00 | September, 20 2015 00:00:00 |       2 |    1 |  66 |   January, 01 2000 00:00:00 |  December, 31 1999 00:00:00 |
| 2134 | September, 16 2015 00:00:00 | September, 17 2015 00:00:00 |       8 |    2 | 159 | September, 16 2015 00:00:00 | September, 17 2015 00:00:00 |
| 4241 | September, 18 2015 00:00:00 | September, 20 2015 00:00:00 |       3 |    2 |  63 |   January, 01 2000 00:00:00 |  December, 31 1999 00:00:00 |

Thanks @MT0, I just arrived to a similiar answer by myself (thanks to a comment in my question). But your answer is really great.. — gabuh, Sep 21 '15 at 10:08

SQL query, create groups by dates

2 Answers2