CODEWITHSUNDEEP
sqloracle-database
Jacek
Jacek

Reputation: 5

SQL spliting rows

Please advice me how I can split records in a way I described below.

I have a table like below

Current data

EMPL | C2 | C3 | DateFrom   | DateTo     | C6 | C7
-----+----+----+------------+------------+----+----
1    | B  | C  | 11/27/2018 | 12/31/9999 | D  | E
2    | B  | C  | 11/27/2018 | 05/31/2019 | D  | E
3    | B  | C  | 11/27/2017 | 06/31/2019 | D  | E

I need to brake every line into two or more records based on current year.

In case of the EMPL 1 I need to split the row in two In one i must enter last day of current year, and the other should have DateFrom set as a first day of next year, but DateTo should be 12/31/9999

In case of EMPL 2 I need to split this record to just two records as shown in the example

Case 3 should produce 3 records, one for each year.

Results

EMPL | C2 | C3 | DateFrom   | DateTo     | C6 | C7
-----+----+----+------------+------------+----+---
1    | B  | C  | 11/27/2018 | 12/31/2018 | D  | E
1    | B  | C  | 01/01/2019 | 12/31/9999 | D  | E
2    | B  | C  | 11/27/2018 | 12/31/2018 | D  | E
2    | B  | C  | 01/01/2019 | 05/31/2019 | D  | E
3    | B  | C  | 11/27/2017 | 12/31/2017 | D  | E
3    | B  | C  | 01/01/2018 | 12/31/2018 | D  | E
3    | B  | C  | 01/01/2019 | 06/31/2019 | D  | E

Upvotes: 0

Views: 83

Answers (1)

MT0
MT0

Reputation: 167774

Oracle Setup:

CREATE TABLE table_name ( EMPL, C2, C3, DateFrom, DateTo, C6, C7 ) AS
SELECT 1, 'B', 'C', DATE '2018-11-27', DATE '9999-12-31', 'D', 'E' FROM DUAL UNION ALL
SELECT 2, 'B', 'C', DATE '2018-11-27', DATE '2019-05-31', 'D', 'E' FROM DUAL UNION ALL
SELECT 3, 'B', 'C', DATE '2017-11-27', DATE '2019-06-30', 'D', 'E' FROM DUAL;

Query:

WITH years ( EMPL, C2, C3, DateFrom, DateTo, C6, C7, YearEnd ) AS (
  SELECT t.*,
         ADD_MONTHS( TRUNC( DateFrom, 'YYYY' ), 12 ) - 1
  FROM   table_name t
UNION ALL
  SELECT EMPL,
         C2,
         C3,
         YearEnd + 1,
         DateTo,
         C6,
         C7,
         CASE WHEN YearEnd + 1 < SYSDATE
         THEN ADD_MONTHS( YearEnd, 12 )
         ELSE DateTo
         END
  FROM   years
  WHERE  YearEnd < DateTo
)
SELECT EMPL,
       C2,
       C3,
       DateFrom,
       LEAST( YearEnd, DateTo ) AS DateTo,
       C6,
       C7
FROM   years
ORDER BY Empl, C2, C3, C6, C7, DateFrom;

Output:

EMPL    C2  C3  DATEFROM    DATETO      C6  C7
----    --  --  ----------  ----------  --  --
1       B   C   2018-11-27  2018-12-31  D   E
1       B   C   2019-01-01  9999-12-31  D   E
2       B   C   2018-11-27  2018-12-31  D   E
2       B   C   2019-01-01  2019-05-31  D   E
3       B   C   2017-11-27  2017-12-31  D   E
3       B   C   2018-01-01  2018-12-31  D   E
3       B   C   2019-01-01  2019-06-30  D   E

Upvotes: 1

Related Questions