1

I'm looking for some tips to find a nice solution for my problem:

Let's say I have a table, where days of vacation of employees are stored. e.g.

CREATE TABLE test_dates (
  id             NUMBER        NOT NULL,
  holidaydate    DATE          NOT NULL,
  holidaycomment VARCHAR2(255) NULL
);

INSERT INTO test_dates VALUES (1, To_Date('17.08.2015','dd.mm.rrrr'), 'Trip to Berlin');
INSERT INTO test_dates VALUES (2, To_Date('18.08.2015','dd.mm.rrrr'), 'Trip to Berlin');
INSERT INTO test_dates VALUES (3, To_Date('19.08.2015','dd.mm.rrrr'), 'Trip to Berlin');
INSERT INTO test_dates VALUES (4, To_Date('20.08.2015','dd.mm.rrrr'), 'Trip to Berlin');
INSERT INTO test_dates VALUES (5, To_Date('21.08.2015','dd.mm.rrrr'), 'Stay at home');
INSERT INTO test_dates VALUES (6, To_Date('03.09.2015','dd.mm.rrrr'), 'Trip to London');
INSERT INTO test_dates VALUES (7, To_Date('04.09.2015','dd.mm.rrrr'), 'Trip to London');
INSERT INTO test_dates VALUES (8, To_Date('07.09.2015','dd.mm.rrrr'), 'Trip to London');
INSERT INTO test_dates VALUES (9, To_Date('08.09.2015','dd.mm.rrrr'), 'Trip to London');
INSERT INTO test_dates VALUES (10, To_Date('17.09.2015','dd.mm.rrrr'), NULL);

SELECT * FROM test_dates ORDER BY holidaydate;

Fine. Now I want to query this table to get the whole period of their holidays.

SELECT
  *
FROM (
  SELECT
    connect_by_root(id) AS id,
    connect_by_root(holidaycomment) AS holidaycomment,
    connect_by_root(holidaydate) AS date_from,
    Decode(connect_by_root(holidaydate), holidaydate, NULL, holidaydate) AS date_to,
    CONNECT_BY_ISLEAF AS is_leaf,
    LEVEL AS days
  FROM test_dates
  CONNECT BY PRIOR holidaydate = holidaydate - 1 AND (PRIOR holidaycomment = holidaycomment OR (PRIOR holidaycomment IS NULL AND holidaycomment IS NULL))
)
WHERE is_leaf = 1
ORDER BY date_from;

That query gave me 10 rows, a way too much (August 17th to 20th, August 18th to 20th and so on). So I'm trying to select the starting dates:

SELECT
  id, holidaycomment, date_from, date_to, days
FROM (
  SELECT
    connect_by_root(id) AS id,
    connect_by_root(holidaycomment) AS holidaycomment,
    connect_by_root(holidaydate) AS date_from,
    Decode(connect_by_root(holidaydate), holidaydate, NULL, holidaydate) AS date_to,
    CONNECT_BY_ISLEAF AS is_leaf,
    LEVEL AS days
  FROM test_dates
  -- NEW Code --
  START WITH id IN (
    SELECT t1.id
    FROM test_dates t1
    LEFT JOIN test_dates t2 ON t1.holidaydate - 1 = t2.holidaydate AND (t1.holidaycomment = t2.holidaycomment OR (t1.holidaycomment IS NULL AND t2.holidaycomment IS NULL))
    WHERE t2.id IS NULL
  )
  -- NEW Code --
  CONNECT BY PRIOR holidaydate = holidaydate - 1 AND (PRIOR holidaycomment = holidaycomment OR (PRIOR holidaycomment IS NULL AND holidaycomment IS NULL))
)
WHERE is_leaf = 1
ORDER BY date_from;
ID  HOLIDAYCOMMENT     DATE_FROM     DATE_TO      DAYS
------------------------------------------------------
1   Trip to Berlin     17.08.2015    20.08.2015   4 
5   Stay at home       21.08.2015    NULL         1
6   Trip to London     03.09.2015    04.09.2015   2
8   Trip to London     07.09.2015    08.09.2015   2
10  NULL               17.09.2015    NULL         1

It works, but it seems to be too complicated for me. Is there a better way for select the date_from?

And my last problem: My/The Application stores no information on weekend (See Ids 6 and 8). So I get 2 rows for one holiday.

Anyone an idea to join these rows with a query and no stored function? I'm looking for this result:

ID  HOLIDAYCOMMENT     DATE_FROM     DATE_TO      DAYS
------------------------------------------------------
1   Trip to Berlin     17.08.2015    20.08.2015   4 
5   Stay at home       21.08.2015    NULL         1
6   Trip to London     03.09.2015    08.09.2015   6
10  NULL               17.09.2015    NULL         1
1

This is my attempt at solving this "groups and islands" problem, with comments in-line:

WITH GroupStart AS
(
    -- Identify the start of each new group
    SELECT 
        TD.id, TD.holidaydate, TD.holidaycomment,
            -- New group if the holidaycomment has changed
            -- (in holidaydate order)
            CASE WHEN holidaycomment =
                    LAG(TD.holidaycomment) OVER (
                        ORDER BY TD.holidaydate)
                THEN 0
                ELSE 1
            END AS gs
    FROM test_dates TD
),
Islands AS
(
    -- The island identifier is the number
    -- of new groups encountered so far
    SELECT 
        id, holidaydate, holidaycomment,  
        SUM(GS.gs) OVER (ORDER BY GS.holidaydate) AS grp
    FROM GroupStart GS
)
-- Results are now a pretty trivial aggregation
SELECT
    MIN(I.id) AS id -- Lowest id in group,
    MIN(I.holidaycomment) AS holidaycomment -- All the same per group,
    MIN(I.holidaydate) AS date_from -- First date in group,
    NULLIF(MAX(I.holidaydate), MIN(I.holidaydate)) AS date_to,
    1 + MAX(I.holidaydate) - MIN(I.holidaydate) AS days
FROM islands I
GROUP BY
    I.grp
ORDER BY
    I.grp,
    date_from;

SQLFiddle

I wrote this as my first Oracle query for about 20 years. The translation above is from my native SQL Server.

| improve this answer | |
  • Thank you, your query cost less than Ditto's but it doesn't work for multiple NULL-Rows (no comment): sqlfiddle I'll try to fix this by myself. – Basti Aug 18 '15 at 7:56
1

Based on Paul White's answer here is my solution. It skips only weekends and groups rows without comment.

WITH GroupStart AS
(
    -- Identify the start of each new group
    SELECT 
        td.id, td.holidaydate, td.holidaycomment, 
            CASE 
              --New group, if more then two days between two dates (more than a weekend)
              WHEN td.holidaydate - LAG(TD.holidaydate) OVER (ORDER BY TD.holidaydate) > 3 
                THEN 1
              --New group, if exactly two days between (maybe a weekend) but "today" is no monday (so there is no weekend between)
              WHEN td.holidaydate - LAG(TD.holidaydate) OVER (ORDER BY TD.holidaydate) = 3 AND NOT To_Char(td.holidaydate, 'd') = '1'   
                THEN 1
              --New group, if exactly 1 day between, this cannot be a weekend
              WHEN td.holidaydate - LAG(TD.holidaydate) OVER (ORDER BY TD.holidaydate) = 2    
                THEN 1
              ELSE  
                CASE 
                  -- New group, if holidaycomment is null and the following holidaycomment is not null 
                  WHEN td.holidaycomment IS NULL AND LAG(TD.holidaycomment) OVER (ORDER BY TD.holidaydate) IS NOT NULL 
                    THEN 1
                  -- New group, if holidaycomment is not null and the following holidaycomment is null 
                  WHEN td.holidaycomment IS NOT NULL AND LAG(TD.holidaycomment) OVER (ORDER BY TD.holidaydate) IS NULL 
                    THEN 1
                  -- New group, if holidaycomment has realy changed (both not null)
                  WHEN td.holidaycomment != LAG(TD.holidaycomment) OVER (ORDER BY TD.holidaydate)
                    THEN 1
                  ELSE 0
              END 
            END AS gs
    FROM test_dates TD
),
Islands AS
(
    -- The island identifier is the number of new groups encountered so far
    SELECT 
        id, holidaydate, holidaycomment,  
        SUM(GS.gs) OVER (ORDER BY GS.holidaydate) AS grp
    FROM GroupStart GS
)
-- Results are now a pretty trivial aggregation
SELECT
    MIN(I.id) AS id, -- Lowest id in group,
    MIN(I.holidaycomment) AS holidaycomment, -- All the same per group,
    MIN(I.holidaydate) AS date_from, -- First date in group,
    NULLIF(MAX(I.holidaydate), MIN(I.holidaydate)) AS date_to,
    1 + MAX(I.holidaydate) - MIN(I.holidaydate) AS days
FROM islands I
GROUP BY I.grp
ORDER BY I.grp, date_from;

SQLFiddle

| improve this answer | |
0

I wouldn't use connect by to solve that, I'd just use some good old analytics :) [edit] based on comment, adjust query - still use analytics, this is NOT a heirarchical query .. [/edit]

  with w_group as (
  select id, sum(grp_cnt) over (order by id) grp_id, 
           holidaydate, holidaycomment
    from (
           select id, holidaydate, holidaycomment,
                  case when holidaycomment = lag(holidaycomment) over (order by holidaydate)
                          then 0 else 1 end  grp_cnt
             from test_dates
        )
  ),
 w_sub as (
     select first_value(id) over (partition by grp_id 
                                   order by holidaydate ) id,
            holidaycomment,
            first_value(holidaydate) over (partition by grp_id 
                                            order by holidaydate ) date_from,
            first_value(holidaydate) over (partition by grp_id 
                                              order by holidaydate desc ) date_to,
            row_number() over (partition by grp_id
                                 order by holidaydate )  rnum
        from w_group
     )
  select id, holidaycomment, date_from, case when date_to = date_from then NULL else date_to end date_to,
         date_to - date_from +1   days
    from w_sub
   where rnum = 1
  order by id
  /

    ID HOLIDAYCOMMENT                           DATE_FROM            DATE_TO                    DAYS

     1 Trip to Berlin                           17-aug-2015 00:00:00 20-aug-2015 00:00:00          4
     5 Stay at home                             21-aug-2015 00:00:00                               1
     6 Trip to London                           03-sep-2015 00:00:00 08-sep-2015 00:00:00          6
    10                                          17-sep-2015 00:00:00                               1
    11 Trip to Berlin                           19-oct-2015 00:00:00 20-oct-2015 00:00:00          2

With clause used to just layer the subquery.

the w_sub subquery just figures out the key info you want (run it by itself to see what it does).

FIRST_VALUE just grabs the first value in the group/partition. By ordering DESC for date_to, we actually grab the last value, however, if you use LAST_VALUE function, it does some funny windowing things ... ;)

the outer query then uses the row_number from the sub query to pick off just the first record in each group/partition ... (at this point each record is the same, it doesn't really matter which one we grab, however, since it seems you want the ID from the first in each group, this works as you want).

| improve this answer | |
  • Thought about that, and it works on the sample data, but it will provide false results when new trips are added with the same holidaycomment value. For example sqlfiddle.com/#!4/e849a/2 – Balazs Papp Aug 17 '15 at 19:04
  • @Balazs: Ok, with that additional requirement, I've altered the query logic ... it's still analytics ... it's not a hierarchical query .. :) – Ditto Aug 17 '15 at 19:16
  • thanks, your query has the same problem as Paul White's attempt. i'll try to improve his solution as it has lower costs on the sample data – Basti Aug 18 '15 at 8:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.