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
This time i actually tested the query :-)
max(overlap) as comm_cnt
select CustomerID, Dat,
sum(event) over (partition by CustomerID
order by dat, event
rows unbounded preceding ) as overlap
CustomerID, StartDate as dat, 1 as event
CustomerID, EndDate as dat, -1 as event
qualify overlap > 1
group by 1
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...
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.