# Finding "islands" of holiday periods for employees

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 --
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```

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.

• 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. Aug 18 '15 at 7:56

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

I wouldn't use connect by to solve that, I'd just use some good old analytics :)  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).

• 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 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 .. :) 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 Aug 18 '15 at 8:11