|
|
|
Forum Newbie
Group: Forum Members
Last Login: 3/27/2008 7:26:00 AM
Posts: 7,
Visits: 9
|
|
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!
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 9:37:55 AM
Posts: 487,
Visits: 217
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 9:37:55 AM
Posts: 487,
Visits: 217
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 9:37:55 AM
Posts: 487,
Visits: 217
|
|
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...
Dieter
|
|
|
|