InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comWed, 03 Dec 2008 16:01:48 GMT20http://www.teradata.com/teradataforum/Topic9664-5-1.aspxThis 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 ) dtgroup by 1But 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...DieterFri, 09 Nov 2007 05:39:43 GMTdnoethhttp://www.teradata.com/teradataforum/Topic9664-5-1.aspxOoops, i can't delete that previous post.I just recognized that it will not work, i'll have to think about another solution.DieterFri, 09 Nov 2007 04:46:25 GMTdnoethhttp://www.teradata.com/teradataforum/Topic9664-5-1.aspxIf 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) dtgroup by 1Another one had to use nested OLAP functions...DieterFri, 09 Nov 2007 04:42:54 GMTdnoethhttp://www.teradata.com/teradataforum/Topic9664-5-1.aspxI need some help with SQL to deal with overlapping dates. Here's the table I haveCustomer ID Communication ID Start Date End Date100 9 1/1/2006 3/30/2006 100 10 2/10/2006 4/10/2006 100 11 5/10/2006 6/30/2006200 9 1/1/2006 3/30/2006 200 12 1/15/2006 3/15/2006300 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/2006I need counts of customers with counts of the communication IDs where the start date and end dates overlap.Results from above would becount of customers count of communication IDs2 21 3Thanks!Thu, 08 Nov 2007 13:09:22 GMTkkoontz