Date Query - please help. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Date Query - please help.

Author

Topic

abarsami
Yak Posting Veteran

68 Posts

Posted - 2003-01-15 : 16:50:25

My table called "Table1"

ID Client_id From_date To_date
1 23 2003-01-15 06:47:06.000 2003-01-15 06:49:10.000
2 23 2003-01-15 06:48:05.000 2003-01-15 06:50:09.000
3 23 2003-01-15 06:48:06.000 2003-01-15 06:53:06.000
4 24 2003-01-16 04:30:06.000 2003-01-15 04:50:06.000
5 24 2003-01-16 04:35:06.000 2003-01-16 04:37:06.000
6 24 2003-01-16 06:35:06.000 2003-01-16 06:37:06.000
7 26 2003-01-17 01:23:06.000 2003-01-17 01:30:06.000
8 26 2003-01-17 01:35:06.000 2003-01-17 01:40:06.000
9 26 2003-01-17 02:50:06.000 2003-01-17 04:23:06.000

I am trying to write a query that finds all overlapping Dates for each Client.
Does anyone know of a fast way of doing this?
So, I'm trying to find more than 1 record that overlaps other records for that Client.
I am trying to find out who has logged in at the same time to a certain site.
Each record is for certain pages on that site.

The query should return those fields who have overlapping dates, so:

It would return

ID
1
2
3
4
5

(id of 6,7,8,9 don't overlap with other records in the table)

Does that make sense?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-15 : 17:07:24

select id ,
(select count(*) from Table1 t2
where ((t2.From_date <= t1.From_date and t2.To_date >= t1.From_date)
or (t2.From_date < t1.To_date and t2.To_date >= t1.To_date)
or (t2.From_date between t1.From_date and t1.To_date))
and t1.Client_id = t2.Client_id
)
from Table1 t1
where exists (select * from Table1 t2
where ((t2.From_date <= t1.From_date and t2.To_date >= t1.From_date)
or (t2.From_date < t1.To_date and t2.To_date >= t1.To_date)
or (t2.From_date between t1.From_date and t1.To_date))
and t1.Client_id = t2.Client_id
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

abarsami
Yak Posting Veteran

68 Posts

Posted - 2003-01-15 : 18:02:50

Thanks, I have to make one change - can you modify the query that you did.

I changed the 1 & 2 & 3 dates where the to_date of ID 1 is equal to the From_date of ID 2.

New results would return

ID
2
3
4
5

(I want to ignore the ones where the To_date is equal to the From_date of the next record. Somewhere in the query I have to remove the equal to sign?)

My table called "Table1"

ID Client_id From_date To_date
1 23 2003-01-15 06:47:06.000 2003-01-15 06:49:10.000
2 23 2003-01-15 06:49:10.000 2003-01-15 06:50:09.000
3 23 2003-01-15 06:49:12.000 2003-01-15 06:53:06.000
4 24 2003-01-16 04:30:06.000 2003-01-15 04:50:06.000
5 24 2003-01-16 04:35:06.000 2003-01-16 04:37:06.000
6 24 2003-01-16 06:35:06.000 2003-01-16 06:37:06.000
7 26 2003-01-17 01:23:06.000 2003-01-17 01:30:06.000
8 26 2003-01-17 01:35:06.000 2003-01-17 01:40:06.000
9 26 2003-01-17 02:50:06.000 2003-01-17 04:23:06.000

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-15 : 18:31:46

Would this be easier ???

SELECT T1.ID
FROM
Table1 T1
INNER JOIN
Table1 T2
ON T2.From_Date BETWEEN T1.From_Date and T1.To_Date
AND T1.ID <> T2.ID

- Jeff

abarsami
Yak Posting Veteran

68 Posts

Posted - 2003-01-15 : 18:55:39

That one doesn't produce the right results.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-15 : 19:36:06

Yes ... it doesn't. I was just wondering if it was easier, not correct.

I actually tested this one:

SELECT DISTINCT T1.ID
FROM
@t T1
INNER JOIN
@t T2
ON
T1.Client = T2.Client AND T1.ID <> T2.ID
WHERE
(T1.From_Date > T2.From_Date AND T1.From_Date < T2.To_Date) OR
(T1.To_Date > T2.From_Date AND T1.To_Date < T2.To_Date) OR
(T1.From_Date < T2.From_Date AND T1.To_Date > T2.To_Date)

by the way, for the results you wanted, I think you need to make the following change in your data:

1 23 2003-01-15 06:47:06.000 2003-01-15 06:49:10.000
2 23 2003-01-15 06:49:10.000 2003-01-15 06:50:09.000
3 23 2003-01-15 06:49:12.000 2003-01-15 06:53:06.000
4 24 2003-01-16 04:30:06.000 2003-01-16 04:50:06.000
5 24 2003-01-16 04:35:06.000 2003-01-16 04:37:06.000
6 24 2003-01-16 06:35:06.000 2003-01-16 06:37:06.000
7 26 2003-01-17 01:23:06.000 2003-01-17 01:30:06.000
8 26 2003-01-17 01:35:06.000 2003-01-17 01:40:06.000
9 26 2003-01-17 02:50:06.000 2003-01-17 04:23:06.000

- Jeff

abarsami
Yak Posting Veteran

68 Posts

Posted - 2003-01-15 : 19:53:56

Cool.

I think it works

Can you explain why you put this line in:

(T1.From_Date < T2.From_Date AND T1.To_Date > T2.To_Date)

I don't understand.

Thanks

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-01-16 : 01:51:19

Hi!

Does this work?

SELECT T1.ID FROM TableName T1
INNER JOIN TableName T2
ON T1.Client = T2.Client AND T1.ID <> T2.ID
WHERE
(T1.From_Date <= T2.To_Date AND T1.To_Date >= T2.From_Date)

Let me know if this works

OS

abarsami
Yak Posting Veteran

68 Posts

Posted - 2003-01-16 : 16:32:31

no that one does not work
- the previous one works.

I have to do some more testing later on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources