CODEWITHSUNDEEP
sqloracle-databasedategaps-and-islands
Kordian Zadrożny
Kordian Zadrożny

Reputation: 55

select periods from date

I have a problem with choosing from the list of absences, those that follow one another and grouping them into periods.

date_from (data_od)  date_to(data_do)
--------------------------
18/08/01 -  18/08/15
18/08/16 -  18/08/20
18/08/21 -  18/08/31
18/09/01 -  18/09/08
18/05/01 -  18/05/31
18/06/01 -  18/06/30
18/03/01 -  18/03/18
18/02/14 -  18/02/28

above is a list of absences, and the result of which should be a table:

date_from (data_od)  date_to(data_do)
--------------------------
18/08/01    18/09/08
18/05/01    18/06/30
18/02/14    18/03/18

For now, I did something like this, but I only research in twos :(

 SELECT u1.data_od,u2.data_do
        FROM l_absencje u1 CROSS APPLY
          (SELECT *  FROM l_absencje labs
              WHERE labs.prac_id=u1.prac_id AND 
               TRUNC(labs.data_od) = TRUNC(u1.data_do)+1
             ORDER BY id DESC FETCH FIRST 1 ROWS ONLY
          ) u2 where u1.prac_id=1067 ;

And give me that:

18/08/01    18/08/20 bad
18/08/16    18/08/31 bad
18/08/21    18/09/08 bad
18/05/01    18/06/30 good 
18/02/14    18/03/18 good

Upvotes: 1

Views: 107

Answers (1)

MT0
MT0

Reputation: 167822

You can use a combination of the LAG(), LEAD() and LAST_VALUE() analytic functions:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE absences ( date_from, date_to ) AS
SELECT DATE '2018-08-01', DATE '2018-08-15' FROM DUAL UNION ALL
SELECT DATE '2018-08-16', DATE '2018-08-20' FROM DUAL UNION ALL
SELECT DATE '2018-08-21', DATE '2018-08-31' FROM DUAL UNION ALL
SELECT DATE '2018-09-01', DATE '2018-09-08' FROM DUAL UNION ALL
SELECT DATE '2018-05-01', DATE '2018-05-31' FROM DUAL UNION ALL
SELECT DATE '2018-06-01', DATE '2018-06-30' FROM DUAL UNION ALL
SELECT DATE '2018-03-01', DATE '2018-03-18' FROM DUAL UNION ALL
SELECT DATE '2018-02-14', DATE '2018-02-28' FROM DUAL;

Query 1:

SELECT *
FROM   (
  SELECT CASE
         WHEN date_to IS NOT NULL
         THEN LAST_VALUE( date_from ) IGNORE NULLS
                OVER( ORDER BY ROWNUM )
         END AS date_from,
         date_to
  FROM   (
    SELECT CASE date_from
           WHEN LAG( date_to ) OVER ( ORDER BY date_to )
                + INTERVAL '1' DAY
           THEN NULL
           ELSE date_from
           END AS date_from,
           CASE date_to
           WHEN LEAD( date_from ) OVER ( ORDER BY date_from )
                - INTERVAL '1' DAY
           THEN NULL
           ELSE date_to
           END AS date_to
    FROM   absences
  )
)
WHERE  date_from IS NOT NULL
AND    date_to IS NOT NULL

Results:

|            DATE_FROM |              DATE_TO |
|----------------------|----------------------|
| 2018-02-14T00:00:00Z | 2018-03-18T00:00:00Z |
| 2018-05-01T00:00:00Z | 2018-06-30T00:00:00Z |
| 2018-08-01T00:00:00Z | 2018-09-08T00:00:00Z |

Upvotes: 2

Related Questions