Hi All,
I have a table which logs calls called calls There are 3 main columns
user_id, starttime,endtime
I would like to run a query (a self-join i guess) which lists all
overlapping calls for a particular user_id.
In theory it should not be possible for a user to make 2 or more
simulataneous calls.
Any help would be greatly appreciated.
SergioYOu should only compare the start time with the data you have to see wheter
it overlaps
Select UserID from YourTable t1
inner join Yourtable t2
on userid = userid
Where t1.Starttime between t2.starttime and t2.endtime
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Serghios" <Serghios.Florides@.elthion.com> schrieb im Newsbeitrag
news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table which logs calls called calls There are 3 main columns
> user_id, starttime,endtime
> I would like to run a query (a self-join i guess) which lists all
> overlapping calls for a particular user_id.
> In theory it should not be possible for a user to make 2 or more
> simulataneous calls.
> Any help would be greatly appreciated.
> Sergio
>
>
>|||Sergio, try,
select c1.userid, c1.starttime as s1, c1.endtime as e1,
c2.starttime as s2, c2.endtime as e2
from calls as c1
join calls as c2
on c1.key_col <> c2.key_col
and c1.userid = c2.userid
and c1.starttime <= c2.endtime
and c1.endtime >= c2.starttime
-- not tested
BG, SQL Server MVP
www.SolidQualityLearning.com
"Serghios" <Serghios.Florides@.elthion.com> wrote in message
news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table which logs calls called calls There are 3 main columns
> user_id, starttime,endtime
> I would like to run a query (a self-join i guess) which lists all
> overlapping calls for a particular user_id.
> In theory it should not be possible for a user to make 2 or more
> simulataneous calls.
> Any help would be greatly appreciated.
> Sergio
>
>
>|||Unfortunately this query returns all rows in my table
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ON1yhjdZFHA.1152@.tk2msftngp13.phx.gbl...
> YOu should only compare the start time with the data you have to see
> wheter it overlaps
> Select UserID from YourTable t1
> inner join Yourtable t2
> on userid = userid
> Where t1.Starttime between t2.starttime and t2.endtime
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Serghios" <Serghios.Florides@.elthion.com> schrieb im Newsbeitrag
> news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
>|||That's because a period always overlaps with itself. You can eliminate the
self match by filtering t1.key <> t2.key, assuming you have a single column
key in the table. If the key is composite, use:
on t1.key1 <> t2.key1 or t1.key2 <> t2.key2, etc.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Serghios" <Serghios.Florides@.elthion.com> wrote in message
news:ODHsQrdZFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Unfortunately this query returns all rows in my table
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:ON1yhjdZFHA.1152@.tk2msftngp13.phx.gbl...
>|||This can be more complicated that it looks on the surface. An overlap can ha
ppen
in one of two ways:
1. The start date of one range is between the start and end date of another
range.
2. The end date of one range is between the start and end date of another ra
nge.
It gets a little more complicated if end dates can be null.
Assuming no nulls, you would have something like:
Select F1..Fn
From Table As T1
Join Table As T2
On T1.PK <> T2.PK
And T1.User_Id = T2.User_Id
And (
(T1.Start >= T2.Start
And (T1.Start <= T2.End Or T1.End <= T2.End))
Or (T2.Start >= T1.Start
And (T2.Start <= T1.End Or T2.End <= T1.End))
)
If the dates can be null, you'll have to decide how that should be interpret
ed.
For example, if a null end date means that there is no end, then you would w
ant
to replace the end date with an arbitrarily large date (e.g. 9999-12-31).
Thomas
"Serghios" <Serghios.Florides@.elthion.com> wrote in message
news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table which logs calls called calls There are 3 main columns
> user_id, starttime,endtime
> I would like to run a query (a self-join i guess) which lists all overlapp
ing
> calls for a particular user_id.
> In theory it should not be possible for a user to make 2 or more simulatan
eous
> calls.
> Any help would be greatly appreciated.
> Sergio
>
>
>|||Think about this in reverse: Two events do not overlap if one ends
before the other begins, or if it begins after the end of other one.
Write that and then negate it.
SELECT E1.*
FROM Events AS E1,
Events AS E2
WHERE E1.event_key <> E2.event_key
AND E1.user_id = E2.user_id
AND NOT (E1.start_time > E2.end_time
OR E1.end_time < E2.end_time)
Or you can use DeMorgan's law on the last predicate:
AND (E1.start_time <= E2.end_time
AND E1.end_time >= E2.end_time)
There is also an OVERLAPS predicate in SQL-92 for this purpose,but it
is a bit tricky.|||Thomas,
Itzik's solution is simpler and equivalent. In words, it may be
easier to think of in terms of two lives. As long as people do
not die before they are born (something you would implement
as a constraint in the data model), the lives of X and Y
overlap if and only if X died after Y was born and Y died after
X was born. It seems too simple to work, but it works. Your
comment about NULL end dates is important. Here's one
solution to handle NULL meaning "not ended yet"
...
and coalesce(X.End,'99991231') > Y.Start
and coalesce(Y.End,'99991231') > X.Start
Steve Kass
Drew University
Thomas Coleman wrote:
>This can be more complicated that it looks on the surface. An overlap can h
appen
>in one of two ways:
>1. The start date of one range is between the start and end date of another
>range.
>2. The end date of one range is between the start and end date of another r
ange.
>It gets a little more complicated if end dates can be null.
>Assuming no nulls, you would have something like:
>
>Select F1..Fn
>From Table As T1
> Join Table As T2
> On T1.PK <> T2.PK
> And T1.User_Id = T2.User_Id
> And (
> (T1.Start >= T2.Start
> And (T1.Start <= T2.End Or T1.End <= T2.End))
> Or (T2.Start >= T1.Start
> And (T2.Start <= T1.End Or T2.End <= T1.End))
> )
>If the dates can be null, you'll have to decide how that should be interpre
ted.
>For example, if a null end date means that there is no end, then you would
want
>to replace the end date with an arbitrarily large date (e.g. 9999-12-31).
>
>Thomas
>
>"Serghios" <Serghios.Florides@.elthion.com> wrote in message
>news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
>
>
>|||On the contrary, the do not produce the same results.
Create Table Dates
(
Id Int Primary Key
, StartDate DateTime
, EndDate DateTime
)
Insert Dates(Id, StartDate, EndDate) Values(1, '20050101', '20050131')
Insert Dates(Id, StartDate, EndDate) Values(2, '20050115', '20050131')
--Original post
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where D1.StartDate Between D2.StartDate And D2.EndDate
--My overlaps version
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where (D1.StartDate >= D2.StartDate
And (D1.StartDate <= D2.EndDate Or D1.EndDate <= D2.EndDate))
Or (D2.StartDate >= D1.StartDate
And (D2.StartDate <= D1.EndDate Or D2.EndDate <= D1.EndDate))
--equivalent to Joe's version
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where Not (D1.StartDate > D2.EndDate Or D1.EndDate < D2.EndDate)
--equivalent to Joe's version using DeMorgan's law
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where D1.StartDate <= D2.EndDate And D1.EndDate >= D2.EndDate
Notice that in the first select statement only Item#2 shows as having an ove
rlap
whereas both values show in the second select statement. The problem with on
ly
looking at the start date on a given row is that it does not account for the
End
Date of that row overlapping with an existing range. Rather, it only account
s
for the overlapped range.
I actually modified my results from the Ovelaps specification in the SQL92 s
pec.
Joe's simplified version is actually cleaner and just as accurate. Thanks Jo
e.
Think I'll use that.
Thomas
"Steve Kass" <skass@.drew.edu> wrote in message
news:uzAJechZFHA.3960@.TK2MSFTNGP10.phx.gbl...
> Thomas,
> Itzik's solution is simpler and equivalent. In words, it may be
> easier to think of in terms of two lives. As long as people do
> not die before they are born (something you would implement
> as a constraint in the data model), the lives of X and Y
> overlap if and only if X died after Y was born and Y died after
> X was born. It seems too simple to work, but it works. Your
> comment about NULL end dates is important. Here's one
> solution to handle NULL meaning "not ended yet"
> ...
> and coalesce(X.End,'99991231') > Y.Start
> and coalesce(Y.End,'99991231') > X.Start
> Steve Kass
> Drew University
>
> Thomas Coleman wrote:
>|||The only downside to your approach Joe is when one range is contained within
another. Granted, we are now getting into the semantics of what it really me
ans
to "overlap". However, if you wanted "overlap or contained within", your
algorithm would not work.
For example, using the structure below imagine we have:
Insert Dates(Id, StartDate, EndDate) Values(1, '20050101', '20050131')
Insert Dates(Id, StartDate, EndDate) Values(2, '20050115', '20050130')
This will show that the first item overlaps the second, but not the reverse.
That can easily be fixed by adding a clause that checks for containment like
so:
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where D1.StartDate <= D2.EndDate And D1.EndDate >= D2.EndDate
Or (D1.StartDate >= D2.StartDate And D1.EndDate <= D2.EndDate)
Thomas
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23tTYa3hZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> On the contrary, the do not produce the same results.
> Create Table Dates
> (
> Id Int Primary Key
> , StartDate DateTime
> , EndDate DateTime
> )
> Insert Dates(Id, StartDate, EndDate) Values(1, '20050101', '20050131')
> Insert Dates(Id, StartDate, EndDate) Values(2, '20050115', '20050131')
> --Original post
> Select *
> From Dates As D1
> Join Dates As D2
> On D1.Id <> D2.Id
> Where D1.StartDate Between D2.StartDate And D2.EndDate
> --My overlaps version
> Select *
> From Dates As D1
> Join Dates As D2
> On D1.Id <> D2.Id
> Where (D1.StartDate >= D2.StartDate
> And (D1.StartDate <= D2.EndDate Or D1.EndDate <= D2.EndDate))
> Or (D2.StartDate >= D1.StartDate
> And (D2.StartDate <= D1.EndDate Or D2.EndDate <= D1.EndDate))
> --equivalent to Joe's version
> Select *
> From Dates As D1
> Join Dates As D2
> On D1.Id <> D2.Id
> Where Not (D1.StartDate > D2.EndDate Or D1.EndDate < D2.EndDate)
> --equivalent to Joe's version using DeMorgan's law
> Select *
> From Dates As D1
> Join Dates As D2
> On D1.Id <> D2.Id
> Where D1.StartDate <= D2.EndDate And D1.EndDate >= D2.EndDate
>
> Notice that in the first select statement only Item#2 shows as having an
> overlap whereas both values show in the second select statement. The probl
em
> with only looking at the start date on a given row is that it does not acc
ount
> for the End Date of that row overlapping with an existing range. Rather, i
t
> only accounts for the overlapped range.
> I actually modified my results from the Ovelaps specification in the SQL92
> spec. Joe's simplified version is actually cleaner and just as accurate.
> Thanks Joe. Think I'll use that.
>
> Thomas
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:uzAJechZFHA.3960@.TK2MSFTNGP10.phx.gbl...
>
Dan Wheeler wrote: