SQL Oracle: concatenating sequential strings

This is a different approach for the same solution. He uses lag()

, lead()

andpartition by

The difference with the previous solution is that this query concatenates strings using consecutive periods that take into account spaces , i.e.

Consider all lines with speed 0.

• line 1: from 01/11 to 14/11
• line 2: from 15/11 to 18/11
• line 3: 20/11 - 22/11

Result:

• line 1: 01/11 to 18/11 ( concatenated lines 1 and 2 )
• line 2: 20/11 - 22/11 (line 3 separated due to break 19/11).

Also note that periods separating the same days as 15 / 11-15 / 11 and 13 / 11-17 / 11 violate this query. The examples of data given have such periods.

-- for better understanding, start reading from the most nested query to the outer

-- QUERY 4: Removes duplicates

-- this query removes duplicates, because both border-rows on a multiple-row period will be identical
-- after the query 3

select distinct
    high_speed,
    speed,
    datefrom,
    dateto,
    dateto-datefrom period
from
(

-- QUERY 3: Selects border-rows and builds datefrom and dateto.

-- this query selects all border-rows, which have the datefrom and dateto data that we need
-- to build the bigger period row.
--
-- this query also builds the bigger period datefrom and dateto

select
    high_speed,
    speed,
    CASE WHEN is_previous_a_border = 0 and is_next_a_border = 1 then lag(datefrom) over (partition by speed order by datefrom)
         WHEN is_previous_a_border = 1 and is_next_a_border = 0 then datefrom
         WHEN is_previous_a_border = 1 and is_next_a_border = 1 then datefrom
         ELSE null END datefrom,
    CASE WHEN is_previous_a_border = 0 and is_next_a_border = 1 then dateto
         WHEN is_previous_a_border = 1 and is_next_a_border = 0 then lead(dateto) over (partition by speed order by datefrom)
         WHEN is_previous_a_border = 1 and is_next_a_border = 1 then dateto
         ELSE null END dateto
from (

-- QUERY 2: Create syntax-sugar

-- this query creates some syntax-sugar properties:
--    - "is_previous_a_border": defines if previous row is a border
--    - "is_next_a_border": defines if previous row is a border

select 
    high_speed,
    speed,
    datefrom,
    dateto,
    is_border,
    nvl(lag(is_border) over (partition by speed order by datefrom), 1) as is_previous_a_border,
    nvl(lead(is_border) over (partition by speed order by datefrom), 1) as is_next_a_border
 from (

-- QUERY 1: Create "is_border" property

-- this query creates the "is_border" property, which defines if a row is a border of a bigger multiple-row period
-- we use partition by to group rows and lag/lead to flag rows with consecutive periods
--
-- note that both border-rows of a bigger multiple-row period will have is_border = 1, while all rows in between
-- them, will have is_border = 0.

select
    high_speed,
    speed,
    datefrom,
    dateto,
    case when lead(datefrom) over (partition by speed order by datefrom) between datefrom and dateto + interval '1' day
         and lag(dateto) over (partition by speed order by datefrom) between datefrom - interval '1' day and dateto then 0
    else 1 end is_border
from
    test))
where is_border = 1)
order by
  speed, datefrom;

      

        
        
        
      

    

SQL Fiddle (with modified fetch data)