Advent of Code - Day 4
Tags: sql, programming, beginners, adventofcode
Published: 2022-12-05 02:59:35

[Day 4 - Camp Cleanup](https://adventofcode.com/2022/day/4) challenge is all about dealing with intervals. Intervals are trickier and more complex than one might think. No surprise the organizer of the Advent Of Code, included them in their challenges.

I did a lot of research on intervals, and specifically _time intervals_ in the past, with a specific focus on how they can be applied to Business Intelligence and more specifically to Fact Table. Couple of slide decks I created lately on the subject are the following:

- [Temporal Snapshot Fact Tables](https://www.slideshare.net/davidemauri/temporal-snapshot-fact-tables)
- [SQL Server 2016 Temporal Tables](https://www.slideshare.net/davidemauri/sql-server-2016-temporal-tables)

As usual I'm importing the input data and storing it into a table. Input data contains _pairs_ of intervals, so I'm splitting the pairs and the interval begin and end into dedicated columns, for easier manipulation:

```sql
create table dbo.ch04_input 
(
    id int identity not null primary key,
    pair1_b int,
    pair1_e int,
    pair2_b int,
    pair2_e int
);

with cteLines as
(
    select 
        trim(replace([value], char(13), '')) as [line]
    from
        string_split(@input, char(10))
),
ctePairs as
(
    select 
        left([line], charindex(',', [line])-1) as pair1,
        right([line], len([line]) - charindex(',', [line])) as pair2
    from
        cteLines
)
insert into 
    dbo.ch04_input (pair1_b, pair1_e, pair2_b, pair2_e)
select
    left([pair1], charindex('-', [pair1])-1)  as pair1_b,
    right([pair1], len([pair1]) - charindex('-', [pair1])) as pair1_e,
    left([pair2], charindex('-', [pair2])-1) as pair2_b,
    right([pair2], len([pair2]) - charindex('-', [pair2])) as pair2_e
from
    ctePairs
```

the result is the following:

![The input data split in begin and end points for each pair](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k2nj05bmr1qh54cz7z81.png)

## Part 1

The challenges ask to find all the pairs where one interval is completely included in the other. We must use the `CONTAINS` operator that can be expressed using simple math:

![Math behind the CONTAINS operator](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m6akxwprrkhpasurswzq.png)

the query, therefore, is:

```sql
select
    count(*) 
from 
    ch04_input
where
    (pair1_b >= pair2_b and pair1_e <= pair2_e) -- pair1 CONTAINS pair2
or
    (pair2_b >= pair1_b and pair2_e <= pair1_e) -- pair2 CONTAINS pair1
```

Challenge solved.

## Part 2 

The second challenge requires to find all the pairs in which interval overlaps. There's an operator for that, and the math is even simpler:

![Math behind the OVERLAPS operator](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iqpyvyrcmva6sx7o8dd0.png)

The query then is:

```sql
select
    count(*)
from 
    ch04_input a
where
    (pair1_b <= pair2_e and pair2_b <= pair1_e) -- OVERLAPS
```

Challenge completed.

## More content

Three friends of mine, [Itzik Ben-Gan](https://www.linkedin.com/in/itzikbengan/), [Dejan Sarka](https://www.linkedin.com/in/dejan-sarka-916641/) and [Adam Machanic](https://www.linkedin.com/in/adammachanic/) were really passionate about this subject, and they shared a lot of content on the "interval" subject over the years.  As the internet continuously changes and evolves, it is not easy to find those articles. Since the challenge was quite quick to finish, I used some of the budgeted time to try to find the updated link to such content. Here you go:

- [Interval Queries in SQL Server](https://www.itprotoday.com/sql-server/interval-queries-sql-server)
- [Intervals and Counts, Part 1](https://www.itprotoday.com/sql-server/intervals-and-counts-part-1)
- [Intervals and Counts, Part 2](https://www.itprotoday.com/sql-server/intervals-and-counts-part-2)
- [Intervals and Counts, Part 3](https://www.itprotoday.com/development-techniques-and-management/intervals-and-counts-part-3)
- [T-SQL Interval Graphs Challenge, Part 1](
https://www.itprotoday.com/sql-server/t-sql-interval-graphs-challenge-part-1)
- [T-SQL Interval Graphs Challenge, Part 2](https://www.itprotoday.com/sql-server/t-sql-interval-graphs-challenge-part-2)
- [TSQL Challenge: Packing Date and Time Intervals](https://www.itprotoday.com/development-techniques-and-management/tsql-challenge-packing-date-and-time-intervals)

And this is an entire session on a subject that is very much related with intervals
- [Temporal Data in SQL Server](https://sqlbits.com/Sessions/Event17/Temporal_Data_in_SQL_Server)
- [Optimizing Temporal Queries](https://sqlbits.com/Sessions/Event12/Optimizing_Temporal_Queries)

Incredibly, I haven't been able to find anything from Adam...it seems the collapse of SqlBlog.com resulted in a lot of lost knowledge :(. I've reached out to him; Here's couple of post that are still available on the subject:

- [Exploring “Fuzzy” Interval Islands Using SQLCLR](http://dataeducation.com/solving-the-net-changes-problem-with-temporal-tables-t-sql-tuesday-087/)
- [Solving the Net Changes Problem with Temporal Tables](http://dataeducation.com/solving-the-net-changes-problem-with-temporal-tables-t-sql-tuesday-087/)