Collapsing result set rows with consecutive values

Question

I have a query which generates the holidays a person has registered on his time sheet. Every day in the time sheet is a separate record, but if you take a week of vacation (26 December 2011 to 30 December 2011), this has to be represented in one block (1 row) by the query.

Here is my query a little simplified for the question:

SELECT -1 ord_li_pers_plan_id
       ,ts.person_id person_id
       ,-1 order_line_id
       ,ts.timesheet_dt start_dt
       ,ts.timesheet_dt end_dt
       ,'Vacation' project
       ,null color
       ,1013011 planning_type
FROM timesheets ts
  JOIN persons pers ON ts.person_id = pers.person_id
  JOIN person_holidays per_hol ON 
    ts.person_holiday_id = per_hol.person_holiday_id
  JOIN v_holiday_types hoty_peho ON 
    per_hol.holiday_type_id = hoty_peho.holiday_type_id
WHERE ts.person_holiday_id IS NOT NULL
AND per_hol.holiday_type_id IN (SELECT holiday_type_id FROM v_holiday_types)
AND ts.person_id = :p_person_id

And here is the output I get now:

-1  11182   -1  30-DEC-11   30-DEC-11   Vacation    1013011
-1  11182   -1  29-DEC-11   29-DEC-11   Vacation    1013011
-1  11182   -1  28-DEC-11   28-DEC-11   Vacation    1013011
-1  11182   -1  27-DEC-11   27-DEC-11   Vacation    1013011
-1  11182   -1  26-DEC-11   26-DEC-11   Vacation    1013011
-1  11182   -1  31-OCT-11   31-OCT-11   Vacation    1013011
-1  11182   -1  02-SEP-11   02-SEP-11   Vacation    1013011
-1  11182   -1  29-JUL-11   29-JUL-11   Vacation    1013011
-1  11182   -1  22-JUL-11   22-JUL-11   Vacation    1013011
-1  11182   -1  25-APR-11   25-APR-11   Vacation    1013011

UPDATE:

Instead, the query should output the following:

-1  11182   -1  26-DEC-11   30-DEC-11   Vacation    1013011
-1  11182   -1  31-OCT-11   31-OCT-11   Vacation    1013011
-1  11182   -1  02-SEP-11   02-SEP-11   Vacation    1013011
-1  11182   -1  29-JUL-11   29-JUL-11   Vacation    1013011
-1  11182   -1  22-JUL-11   22-JUL-11   Vacation    1013011
-1  11182   -1  25-APR-11   25-APR-11   Vacation    1013011

Note the first row. It now represents the whole vacation.

UPDATE 2

My client came up with another idea. This query is linked to a timesheet with all projects a person is assigned to. The project dates were originally stored as a block (1 record for per date range ex. 01/01/2012 - 10/01/2012 => 1 record). Now the client wants to store all of the dates as separate records. That's not hard to do and that works.

The problem I now face is that the group by doesn't work anymore because the ord_li_pers-plan_id is the PK.

Attached is the query. It's a rather large one.

    SELECT person_id ,
    order_line_id ,
    MIN(start_dt) start_dt ,
    MAX(end_dt) end_dt ,
    project ,
    color ,
    planning_type,
    ord_li_pers_plan_id
  FROM
    (SELECT ord_li_pers_plan_id ,
      person_id ,
      order_line_id ,
      start_dt ,
      end_dt ,
      project ,
      color ,
      planning_type ,
      SUM(gap) OVER (PARTITION BY person_id ORDER BY start_dt) contiguous_grp
    FROM
      (SELECT ord_li_pers_plan_id ,
        person_id ,
        order_line_id ,
        start_dt ,
        end_dt ,
        project ,
        color ,
        planning_type ,
        CASE
          WHEN lag(end_dt) over(PARTITION BY person_id ORDER BY start_dt) + 1 >= start_dt
          THEN 0
          ELSE 1
        END gap
      FROM
        (SELECT ord_li_pers_plan_id ,
          person_id ,
          order_line_id ,
          start_dt ,
          end_dt ,
          project ,
          color ,
          planning_type
        FROM
          (SELECT op.ord_li_pers_plan_id ord_li_pers_plan_id ,
            p.person_id person_id ,
            p.order_line_id order_line_id ,
            op.start_dt start_dt ,
            op.end_dt end_dt ,
            pl$planning.prep_tooltip(NVL2(ord.end_customer_id, end_cus.name, cus.name)
            || ' - '
            || NVL2(ord_li.project_cd,ord_li.project_cd,ord.project_cd)
            || ' - '
            || func_tp.name
            || ' - '
            || ROUND((con_tp.contract_tp / 5), 2)
            ||' - '
            || bl$gen_codes.Name_by_Code_Id (op.planning_type)) project ,
            olc.color color ,
            op.planning_type planning_type
          FROM order_line_person_planning op
          JOIN order_line_persons p
          ON p.ORDER_LINE_PERSON_ID = op.ORDER_LINE_PERSON_ID
          JOIN order_lines ord_li
          ON ord_li.order_line_id = p.order_line_id
          JOIN orders ord
          ON ord.order_id = ord_li.order_id
          LEFT JOIN order_line_colors olc
          ON olc.order_line_id = ord_li.order_line_id
          JOIN customers cus
          ON ord.customer_id=cus.customer_id
          LEFT JOIN customers end_cus
          ON ord.end_customer_id=end_cus.customer_id
          LEFT JOIN v_contract_types con_tp
          ON ord.contract_type_id=con_tp.contract_type_id
          JOIN v_function_types func_tp
          ON ord_li.function_id=func_tp.function_id
          UNION
          SELECT -1 ord_li_pers_plan_id ,
            ts.person_id person_id ,
            -1 order_line_id ,
            ts.timesheet_dt start_dt ,
            ts.timesheet_dt end_dt ,
            'Vacation' project ,
            '#99FF33' color ,
            -1 planning_type
          FROM hrm_iadvise.timesheets ts
          JOIN hrm_iadvise.persons pers
          ON ts.person_id = pers.person_id
          JOIN hrm_iadvise.person_holidays per_hol
          ON ts.person_holiday_id = per_hol.person_holiday_id
          JOIN hrm_iadvise.v_holiday_types hoty_peho
          ON per_hol.holiday_type_id  = hoty_peho.holiday_type_id
          WHERE ts.person_holiday_id IS NOT NULL
          UNION
          SELECT -1 ord_li_pers_plan_id ,
            per_hol.person_id person_id ,
            -1 order_line_id ,
            hol.dt start_dt ,
            hol.dt end_dt ,
            'Vacation' project ,
            '#99FF33' color ,
            -1 planning_type
          FROM holidays hol
          JOIN person_holidays per_hol
          ON per_hol.holiday_type_id = hol.holiday_type_id
          JOIN countries coty
          ON coty.country_id    = hol.country_id
          WHERE coty.country_cd = 150
          )
        )
      )
    )
  GROUP BY person_id ,
    order_line_id ,
    project ,
    color ,
    planning_type ,
    contiguous_grp,
    ord_li_pers_plan_id;

The first query gets all the projects a person is assigned to The second query gets all the holidays a person has already registered The third query gets all of the national holidays

Thanks in advance

Answer 1

You can use analytics. Data is read only once:

SQL> WITH your_query AS (
  2     SELECT 11182 person_id, to_date('30-DEC-11') start_dt,
  3            to_date('30-DEC-11') end_dt FROM dual
  4  UNION ALL SELECT 11182,to_date('29-DEC-11'),to_date('29-DEC-11') FROM DUAL
  5  UNION ALL SELECT 11182,to_date('28-DEC-11'),to_date('28-DEC-11') FROM DUAL
  6  UNION ALL SELECT 11182,to_date('27-DEC-11'),to_date('27-DEC-11') FROM DUAL
  7  UNION ALL SELECT 11182,to_date('26-DEC-11'),to_date('26-DEC-11') FROM DUAL
  8  UNION ALL SELECT 11182,to_date('31-OCT-11'),to_date('31-OCT-11') FROM DUAL
  9  UNION ALL SELECT 11182,to_date('02-SEP-11'),to_date('02-SEP-11') FROM DUAL
 10  UNION ALL SELECT 11182,to_date('29-JUL-11'),to_date('29-JUL-11') FROM DUAL
 11  UNION ALL SELECT 11182,to_date('22-JUL-11'),to_date('22-JUL-11') FROM DUAL
 12  UNION ALL SELECT 11182,to_date('25-APR-11'),to_date('25-APR-11') FROM DUAL
 13  )
 14  SELECT person_id, MIN(start_dt) start_dt, MAX(end_dt) end_dt
 15    FROM (SELECT person_id, start_dt, end_dt,
 16                 SUM(gap) over(PARTITION BY person_id
 17                               ORDER BY start_dt) contiguous_grp
 18             FROM (SELECT person_id, start_dt, end_dt,
 19                           CASE WHEN lag(end_dt) over(PARTITION BY person_id
 20                                     ORDER BY start_dt) + 1 >= start_dt
 21                              THEN 0 ELSE 1
 22                           END gap
 23                      FROM (SELECT * FROM your_query)))
 24   GROUP BY person_id, contiguous_grp
 25   ORDER BY 1, 2 DESC;
 PERSON_ID START_DT  END_DT
---------- --------- ---------
     11182 26-DEC-11 30-DEC-11
     11182 31-OCT-11 31-OCT-11
     11182 02-SEP-11 02-SEP-11
     11182 29-JUL-11 29-JUL-11
     11182 22-JUL-11 22-JUL-11
     11182 25-APR-11 25-APR-11

Answer 2

You need to use a hierarchical query to achieve this. Use something like this:

SELECT
    person_id,
    person_holiday_id,
    MIN(BEGIN),
    END
FROM
(
    SELECT
        person_id,
        person_holiday_id,
        connect_by_root(timesheet_dt) AS BEGIN,
        timesheet_dt AS END,
        connect_by_isleaf AS is_leaf
    FROM
        timesheets
        CONNECT BY
            PRIOR TRUNC(timesheet_dt) + 1 = TRUNC(timesheet_dt)
            AND PRIOR person_id = person_id
)
WHERE
    is_leaf = 1
GROUP BY
    person_id,
    person_holiday_id,
    END
;

It returns one row per continues vacation period per user. Put this in a view and use it instead of the timesheets table in the query you posted in your question.

Please note:

1. This query will not have top performance, because every row in timesheets is used as the root row of the hierarchical query and possibly as a leaf row and an intermediate row. You can see this when you compare run the sub query and compare how many rows it returns to the how many rows the complete query returns.
2. You might improve performance if you put an index on timesheet_dt and make sure that it never contains a time part - then you can get rid of the TRUNC functions.
3. If Saturdays and Sundays are not entered in the timesheets table, the maximum vacation period this statement will return is one week from Monday to Friday. If this is a problem, the CONNECT BY clause needs to be adjusted. However, this will not solve the problems of public holidays not being entered into the timesheets table...