Overlapping Date SQL

Analytics

Overlapping Date SQL

I need some help with SQL to deal with overlapping dates. Here's the table I have

Customer ID Communication ID Start Date End Date
100 9 1/1/2006 3/30/2006
100 10 2/10/2006 4/10/2006
100 11 5/10/2006 6/30/2006
200 9 1/1/2006 3/30/2006
200 12 1/15/2006 3/15/2006
300 9 1/1/2006 3/30/2006
400 9 1/1/2006 3/30/2006
400 10 2/10/2006 4/10/2006
400 12 1/15/2006 3/15/2006

I need counts of customers with counts of the communication IDs where the start date and end dates overlap.

Results from above would be

count of customers   count of communication IDs
2 2
1 3

Thanks!

4 REPLIES
N/A

Re: Overlapping Date SQL

If the number of rows per CustomerID is low there's a simple solution:

select cnt, count(*)
from
(
select t1.CustomerID, count(*) as cnt
from tab t1 join tab t2
on t1.CustomerID = t2.CustomerID
and t1.CommunicationID < t2.CommunicationID
where (t1.StartDate, t1.EndDate) overlaps (t2.StartDate, t2.EndDate)
group by 1
) dt
group by 1

Another one had to use nested OLAP functions...

Dieter
N/A

Re: Overlapping Date SQL

Ooops, i can't delete that previous post.
I just recognized that it will not work, i'll have to think about another solution.

Dieter
N/A

Re: Overlapping Date SQL

This time i actually tested the query :-)

select
comm_cnt,
count(*)
from
(
select CustomerID,
max(overlap) as comm_cnt
from
(
select CustomerID, Dat,
sum(event) over (partition by CustomerID
order by dat, event
rows unbounded preceding ) as overlap
from
(
Select
CustomerID, StartDate as dat, 1 as event
From U999999.dropme
union all
Select
CustomerID, EndDate as dat, -1 as event
From U999999.dropme
) dt
qualify overlap > 1
)dt
group by 1
) dt
group by 1

But you didn't tell what's the expected result set if there are several seperate overlapping date ranges for the same customer, e.g. first with 3 overlapping and second with 2 overlapping.

My query just uses the maximum number. if you want to count both there must be 2? OLAP steps...

SS7
N/A

Re: Overlapping Date SQL

Hi Dnoeth,

I went through the solution given & its really good approach. But I was wondering if I can get the overlapping record values using similar approach. Please suggest.