establish duplicate logs

i've got a table like this...
date connected disconnected
10/02/2005 10:03:33 10:05:34
10/02/2005 14:11:00 15:22:01
10/02/2005 14:30 15:00:00
how can i check what connections overlapped in sql?
e.g. result would be (connected at the same time):
10/02/2005 14:11:00 15:22:01
10/02/2005 14:30 15:00:00Nemo
Look at this example ( it will give an idea) written by Itzik Ben-Gan
create table tblConnection
(
StartTimeCon datetime not null,
EndTimeCon datetime not null
)
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:00','20000610 10:10')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:20','20000610 10:22')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:23','20000610 10:25')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:27','20000610 10:45')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
11:57','20000610 12:00')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
12:01','20000610 12:04')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
12:04','20000610 12:40')
select * from tblConnection
SELECT
StartTimeCon,
ISNULL(
(SELECT MIN(EndTimeCon)
FROM tblConnection AS S3
WHERE S3.StartTimeCon >= S1.StartTimeCon
AND ISNULL(
DATEDIFF(
minute,
S3.EndTimeCon,
(SELECT MIN(StartTimeCon)
FROM tblConnection AS S4
WHERE S4.StartTimeCon > S3.EndTimeCon)), 5) >= 5),
EndTimeCon) AS EndTimeCon
FROM tblConnection AS S1
WHERE ISNULL(
DATEDIFF(
minute,
(SELECT MAX(EndTimeCon)
FROM tblConnection AS S2
WHERE S2.EndTimeCon < S1.StartTimeCon),S1.StartTimeCon),5) >= 5
"Nemo" <blogmeester@.gmail.com> wrote in message
news:dcqljb$6qs$2@.ctb-nnrp2.saix.net...
> i've got a table like this...
> date connected disconnected
> 10/02/2005 10:03:33 10:05:34
> 10/02/2005 14:11:00 15:22:01
> 10/02/2005 14:30 15:00:00
> how can i check what connections overlapped in sql?
>
> e.g. result would be (connected at the same time):
> 10/02/2005 14:11:00 15:22:01
> 10/02/2005 14:30 15:00:00
>
>
>|||Try,
What format are you using for column [date]?
Is there any primary key in this table that we can use to uniquely identify
each row?

> 10/02/2005
Example:
use northwind
go
create table t1 (
pk_col int not null identity primary key,
[date] varchar(10),
connected char(8),
disconnected char(8)
)
go
insert into t1([date], connected, disconnected) values('10/02/2005',
'10:03:33', '10:05:34')
insert into t1([date], connected, disconnected) values('10/02/2005',
'14:11:00', '15:22:01')
insert into t1([date], connected, disconnected) values('10/02/2005',
'14:30:00', '15:00:00')
go
select * from t1
go
create view dbo.v1
as
select
pk_col, [date], connected, disconnected,
cast(right([date], 4) + '-' + left([date], 2) + '-' + substring([date],
4, 2) + 'T' + connected as datetime) as c1,
cast(right([date], 4) + '-' + left([date], 2) + '-' + substring([date],
4, 2) + 'T' + disconnected as datetime) as c2
from t1
go
select
[date], connected, disconnected
from
dbo.v1 as a
where
exists(select * from dbo.v1 as b where a.pk_col != b.pk_col and a.c1 <=
b.c2 and a.c2 >= b.c1)
or
exists(select * from dbo.v1 as b where a.pk_col != b.pk_col and b.c1 <=
a.c2 and b.c2 >= a.c1)
go
drop view dbo.v1
go
drop table t1
go
AMB
"Nemo" wrote:

> i've got a table like this...
> date connected disconnected
> 10/02/2005 10:03:33 10:05:34
> 10/02/2005 14:11:00 15:22:01
> 10/02/2005 14:30 15:00:00
> how can i check what connections overlapped in sql?
>
> e.g. result would be (connected at the same time):
> 10/02/2005 14:11:00 15:22:01
> 10/02/2005 14:30 15:00:00
>
>
>|||On Wed, 3 Aug 2005 16:50:18 +0200, Nemo wrote:

>i've got a table like this...
>date connected disconnected
>10/02/2005 10:03:33 10:05:34
>10/02/2005 14:11:00 15:22:01
>10/02/2005 14:30 15:00:00
>how can i check what connections overlapped in sql?
>
>e.g. result would be (connected at the same time):
>10/02/2005 14:11:00 15:22:01
>10/02/2005 14:30 15:00:00
Hi Nemo,
Storing date and time in seperate columns is actually not a good idea.
What if a connection begins before midnight, but ends after midnight?
And what if the time between connecting and disconnecting is more than
24 hours?
If you remove the seperate date column and store connected and
disconnected as datetime values, the query to find the overlaps is easy:
SELECT a.connected, a.disconnected
FROM MyTable AS a
WHERE EXISTS
(SELECT *
FROM MyTable AS b
WHERE b.disconnected > a.connected
AND b.connected < a.disconnected)
Depending on your definition of overlap, you might have to change the <
and > operators to <= and >=.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||thanks, followed the advice.
Will combine the date and time fields.
think my table structures nneed some work
regards
Nemo
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:evb2f1db2obpa231qjk858633q1gvnccj8@.
4ax.com...
> On Wed, 3 Aug 2005 16:50:18 +0200, Nemo wrote:
>
> Hi Nemo,
> Storing date and time in seperate columns is actually not a good idea.
> What if a connection begins before midnight, but ends after midnight?
> And what if the time between connecting and disconnecting is more than
> 24 hours?
> If you remove the seperate date column and store connected and
> disconnected as datetime values, the query to find the overlaps is easy:
> SELECT a.connected, a.disconnected
> FROM MyTable AS a
> WHERE EXISTS
> (SELECT *
> FROM MyTable AS b
> WHERE b.disconnected > a.connected
> AND b.connected < a.disconnected)
> Depending on your definition of overlap, you might have to change the <
> and > operators to <= and >=.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)sql