CODEWITHSUNDEEP
mysqlsql-servert-sql
SajjadZare
SajjadZare

Reputation: 2378

Write a query for overlapping time in this example

Imagine that i have a table with below data: enter image description here

I want to write a query that give: customers that have overlapping time for example : 

Row 2,3
because customer of these are the same and the overlapping time

Upvotes: 2

Views: 39

Answers (2)

mohan111
mohan111

Reputation: 8865

SELECT a.Row
      ,a.customer
      ,a.start
       ,a.end
      ,b.ColumnId AS OverlappedId 
      ,b.InTime   AS OverlappedInTime 
      ,b.OutTime  AS OverlappedOutTime 
  FROM Time  AS a 
  JOIN Time  AS b ON ((a.start BETWEEN b.start AND b.end) 
                       OR  (a.end BETWEEN b.start AND b.end)) 
                      AND  (a.row!= b.row)

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

Here is an approach using SELF-JOIN. This may not be efficient for large tables:

SELECT t1.* 
FROM TestData t1
INNER JOIN TestData t2
    ON t2.Customer = t1.Customer
    AND t2.EndDate >= t1.StartDate
    AND t2.StartDate <= t1.EndDate
    AND t1.Row <> t2.Row

SQL Fiddle

Upvotes: 1

Related Questions