find records who share the same dates

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)

find records who share the same dates

Author

Topic

cardgunner

326 Posts

Posted - 2008-06-19 : 15:43:36

I need to find any record that may share the same date in a date range. A better word may be overlapping.

What records overlap the other.


declare @rcd_id varchar(3)
declare @s_date datetime
declare @e_date datetime

select 'A' rcd_id,'05/01/2008' s_date,'05/21/2008'e_date union all
select 'A' rcd_id,'04/15/2008' s_date,'05/15/2008'e_date union all
select 'B' rcd_id,'05/01/2008' s_date,'05/21/2008'e_date union all
select 'B' rcd_id,'05/15/2008' s_date,'06/01/2008'e_date union all
select 'C' rcd_id,'05/05/2008' s_date,'05/21/2008'e_date union all
select 'C' rcd_id,'05/24/2008' s_date,'05/31/2008'e_date union all
select 'D' rcd_id,'05/02/2008' s_date,'05/20/2008'e_date union all
select 'A' rcd_id,'05/03/2008' s_date,'05/10/2008'e_date union all
select 'B' rcd_id,'06/03/2008' s_date,'06/21/2008'e_date union all
select 'A' rcd_id,'06/15/2008' s_date,'06/21/2008'e_date  

RESULTS
rcd_id s_date     e_date     
------ ---------- ---------- 
A      05/01/2008 05/21/2008
A      04/15/2008 05/15/2008
B      05/01/2008 05/21/2008
B      05/15/2008 06/01/2008
C      05/05/2008 05/21/2008
C      05/24/2008 05/31/2008
D      05/02/2008 05/20/2008
A      05/03/2008 05/10/2008
B      06/03/2008 06/21/2008
A      06/15/2008 06/21/2008

(10 row(s) affected)

DESIRED RESULTS

RCD_ID CNT  
------ ---- 
A      3
B      2

I need to identify which records overlap by the id.

I have thousands and thousands of records to search to find the ones that overlap.

CardGunner

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-06-19 : 16:50:25

[code]if object_id('tempdb..#table') is not null
drop table #table
create table #table ( i int identity, rcd_id CHAR(1), s_date datetime, e_date datetime )
insert #table ( rcd_id, s_date, e_date )
select rcd_id, s_date, e_date from YourTable

select t2.rcd_id, count(*) as cnt
from ( select *
from #table
) t1
join ( select *
from #table
) t2 on t1.rcd_id = t2.rcd_id
and t1.i < t2.i
and ( t2.s_date between t1.s_date and t1.e_date
or t2.e_date between t1.s_date and t1.e_date
)
group by t2.rcd_id[/code]

cardgunner

326 Posts

Posted - 2008-06-20 : 08:11:48

That gave me



rcd_id cnt         
------ ----------- 
A                3 
B                1

Which may work. I'm trying it now with my real tables

CardGunner

cardgunner

326 Posts

Posted - 2008-06-20 : 09:18:49

The only hiccup I've found is if the start date on of one record is the same as the end date then it counts it as an overlap.

Other then that it seems to be working great with only 10 out of 111 tested there are 6 bonafide overlaps and 4 sames dates.

Thank you very much.

If you can come up with a way not count if the start date of one and the end of the other are the same please let me know.

But the result set I have is workable. Thank you. Saved me a ton of time.

CardGunner

cardgunner

326 Posts

Posted - 2008-06-20 : 09:39:49

I added

and t2.s_date<>t1.e_date

to make the statement like this


select  t2.rcd_id, count(*) as cnt
from    ( select    *
          from      #table
        ) t1
join    ( select    *
          from      #table
        ) t2 on t1.rcd_id = t2.rcd_id
                and t1.i < t2.i
                and ( t2.s_date between t1.s_date and t1.e_date
                      or t2.e_date between t1.s_date and t1.e_date
                    )
		and t2.s_date<>t1.e_date 
group by t2.rcd_id

It eliminated the records where the start and end was the same.

I'm not a pro at this, obviously.

Does anyone see a problem with the ending to eliminate the same dates?

CardGunner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 10:08:28

[code]SELECT t1.rcd_id,count(*) + 1
FROM Table t1
INNER JOIN Table t2
ON t2.rcd_id=t1.rcd_id
AND (t2.s_date <=t1.s_date AND t2.e_date >t1.s_date)
OR (t2.s_date<=t1.e_date AND t2.s_date >t1.s_date)
GROUP BY t1.rcd_id
[/code]

cardgunner

326 Posts

Posted - 2008-06-20 : 11:27:01

Sorry Visakh but yours doesn't get me the reults.

This is what I used


SELECT 	t1.rcd_id,count(*) + 1 
FROM 	( select    *
          from      tmptable
        ) t1  
	inner JOIN 
	 (select    *
          from      tmptable
        ) t2
ON t2.rcd_id=t1.rcd_id
AND (t2.s_date <=t1.s_date AND t2.e_date >t1.s_date)
OR (t2.s_date<=t1.e_date AND t2.s_date >t1.s_date)
GROUP BY t1.rcd_id

sample results
cnt         rcd_id           
----------- ---------------- 
         70 21014           
        715 21665           
        268 22068           
         85 22356           
         72 23501           
        712 23578           
        767 23842           
        792 23939           
       1152 24139           
       1060 25044           
        545 25480           
        912 25648           
        520 25858           
       2454 26124 

(926 row(s) affected)

Somethings not right

CardGunner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 11:43:58

cardgunner

326 Posts

Posted - 2008-06-20 : 12:21:17

That is 941 records.

if I change the last to be

AND (t2.s_date <=t1.s_date AND t2.e_date >t1.s_date
OR t2.s_date<=t1.e_date AND t2.e_date >t1.s_date)
that is 926 records.

If I add

and t1.i<t2.i

that is 108 records

CardGunner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 13:55:41

quote:
Originally posted by cardgunner

That is 941 records.

if I change the last to be

AND (t2.s_date <=t1.s_date AND t2.e_date >t1.s_date
OR t2.s_date<=t1.e_date AND t2.e_date >t1.s_date)
that is 926 records.

If I add

and t1.i<t2.i

that is 108 records

CardGunner

What's the count you're expecting?

cardgunner

326 Posts

Posted - 2008-06-20 : 14:29:53

Sorry, 111 was the number.

Counting that if the end date of a record was the same as the start date of another record of the same rcd_id then it counted it as a overlap when in fact it is not. So I added to the where clause of jdaman's example
and t2.s_date<>t1.e_date
to get 44.

Out of those 44 I have looked up half and those 22 records are bonafide overlaps.
Now I'm trying to figure out how to exclude the one or more records that are overlapping and keep the one I want. To keep the one that is correct.
The key is trying to define which one do you want to keep. what seperates that one from the others.Arggghhhh!

I was not sure, and still not sure if my additional clause was hiding something I may be wanting to count unintionally cause I'm not a pro at this.

Kind of like counting a year as 365 without considering the leap year day, you know what I mean?

CardGunner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 14:41:54

[code]DECLARE @Sample TABLE (rcd_id VARCHAR(3), s_date DATETIME, e_date DATETIME)

SET DATEFORMAT MDY

INSERT @Sample
SELECT 'A', '05/01/2008', '05/21/2008' UNION ALL
SELECT 'A', '04/15/2008', '05/15/2008' UNION ALL
SELECT 'B', '05/01/2008', '05/21/2008' UNION ALL
SELECT 'B', '05/15/2008', '06/01/2008' UNION ALL
SELECT 'C', '05/05/2008', '05/21/2008' UNION ALL
SELECT 'C', '05/24/2008', '05/31/2008' UNION ALL
SELECT 'D', '05/02/2008', '05/20/2008' UNION ALL
SELECT 'A', '05/03/2008', '05/10/2008' UNION ALL
SELECT 'B', '06/03/2008', '06/21/2008' UNION ALL
SELECT 'A', '06/15/2008', '06/21/2008'

SELECT rcd_id AS RCD_ID,
COUNT(*) AS CNT
FROM (
SELECT DISTINCT s1.rcd_id,
s1.s_date,
s1.e_date
FROM @Sample AS s1
INNER JOIN @Sample AS s2 ON s2.rcd_id = s1.rcd_id
WHERE s2.e_date >= s1.s_date
AND s2.s_date <= s1.e_date
AND s1.s_date <> s2.s_date
AND s1.e_date <> s2.e_date
) AS d
GROUP BY rcd_id
ORDER BY rcd_id[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 15:02:04

Maybe better in case of duplicates.
Same sample data as above.

SELECT		s1.rcd_id AS RCD_ID,
		COUNT(*) AS CNT
FROM		@Sample AS s1
WHERE		EXISTS	(	SELECT	*
				FROM	@Sample AS s2
				WHERE	s2.rcd_id = s1.rcd_id
					AND s2.e_date >= s1.s_date
					AND s2.s_date <= s1.e_date
					AND s1.s_date <> s2.s_date
					AND s1.e_date <> s2.e_date
			)
GROUP BY	s1.rcd_id
ORDER BY	s1.rcd_id

E 12°55'05.25"
N 56°04'39.16"

cardgunner

326 Posts

Posted - 2008-06-20 : 16:08:47

Thanks Peso but both of yours counted two records of the same rcd_id as being overlapping but none did however one record for that tcd_id start date and one record for the end date was the same. So I'm assuming it counted them as overlapping. I could not see not other overlap. Here is the sample from the first one I tested.


t_clot           t_rsdt                                                 t_rrdt                                                 
---------------- ------------------------------------------------------ ------------------------------------------------------ 
23330            2007-05-10 12:00:00.000                                2007-06-22 12:00:00.000
23330            2007-06-25 12:00:00.000                                2007-06-28 12:00:00.000
23330            2007-07-11 12:00:00.000                                2007-07-12 12:00:00.000
23330            2007-07-12 12:00:00.000                                2007-07-18 12:00:00.000
23330            2007-07-23 12:00:00.000                                2007-07-30 12:00:00.000
23330            2007-08-21 12:00:00.000                                2007-10-16 12:00:00.000
23330            2007-10-23 12:00:00.000                                2007-11-27 13:00:00.000
23330            2008-01-14 13:00:00.000                                2008-01-21 13:00:00.000

Both of your statements counted this as 2.

CardGunner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 16:17:25

The two overlapping records are

rcd_id	s_date			e_date			rcd_id	s_date			e_date
------	-----------------------	-----------------------	------	-----------------------	-----------------------
23330	2007-07-12 12:00:00.000	2007-07-18 12:00:00.000	23330	2007-07-11 12:00:00.000	2007-07-12 12:00:00.000
23330	2007-07-11 12:00:00.000	2007-07-12 12:00:00.000	23330	2007-07-12 12:00:00.000	2007-07-18 12:00:00.000

E 12°55'05.25"
N 56°04'39.16"

cardgunner

326 Posts

Posted - 2008-06-20 : 16:19:04

Peso Your did count one that was correct that was not on jdaman's and the one that it counted was overlapped.

Hmm

CardGunner

cardgunner

326 Posts

Posted - 2008-06-20 : 16:36:01

quote:

Originally posted by Peso

The two overlapping records are

rcd_id	s_date			e_date			rcd_id	s_date			e_date
------	-----------------------	-----------------------	------	-----------------------	-----------------------
23330	2007-07-12 12:00:00.000	2007-07-18 12:00:00.000	23330	2007-07-11 12:00:00.000	2007-07-12 12:00:00.000
23330	2007-07-11 12:00:00.000	2007-07-12 12:00:00.000	23330	2007-07-12 12:00:00.000	2007-07-18 12:00:00.000

E 12°55'05.25"
N 56°04'39.16"

Not for me they don't. One record started 7/11 at 12:00 and ended 7/12 at 12:00. The other started 7/12 at 12:00 and ended 7/18.

I guess 2 records can't occupy the same exact time?

In my world they can and I because of that I do not want to count them as overlapping.

Also I found 3 records in yours that I did not have before. But I have at least 18 that I consider to be over lapping.

Thanks for the alternative.

CardGunner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 16:38:48

So please inform us about your business rules when it comes to overlapping.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 16:40:49

[code]DECLARE @Sample TABLE (rcd_id VARCHAR(5), s_date DATETIME, e_date DATETIME)

SET DATEFORMAT MDY

INSERT @Sample
SELECT '23330', '2007-05-10 12:00:00.000', '2007-06-22 12:00:00.000' UNION ALL
SELECT '23330', '2007-06-25 12:00:00.000', '2007-06-28 12:00:00.000' UNION ALL
SELECT '23330', '2007-07-11 12:00:00.000', '2007-07-12 12:00:00.000' UNION ALL
SELECT '23330', '2007-07-12 12:00:00.000', '2007-07-18 12:00:00.000' UNION ALL
SELECT '23330', '2007-07-23 12:00:00.000', '2007-07-30 12:00:00.000' UNION ALL
SELECT '23330', '2007-08-21 12:00:00.000', '2007-10-16 12:00:00.000' UNION ALL
SELECT '23330', '2007-10-23 12:00:00.000', '2007-11-27 13:00:00.000' UNION ALL
SELECT '23330', '2008-01-14 13:00:00.000', '2008-01-21 13:00:00.000'

SELECT *
FROM @Sample AS s1
INNER JOIN @Sample AS s2 ON s2.rcd_id = s1.rcd_id
WHERE s2.e_date >= s1.s_date
AND s2.s_date <= s1.e_date
AND s1.s_date <> s2.s_date
AND s1.e_date <> s2.e_date

SELECT rcd_id AS RCD_ID,
COUNT(*) AS CNT
FROM (
SELECT DISTINCT s1.rcd_id,
s1.s_date,
s1.e_date
FROM @Sample AS s1
INNER JOIN @Sample AS s2 ON s2.rcd_id = s1.rcd_id
WHERE s2.e_date > s1.s_date
AND s2.s_date < s1.e_date
AND s1.s_date <> s2.s_date
AND s1.e_date <> s2.e_date
) AS d
GROUP BY rcd_id
ORDER BY rcd_id

SELECT s1.rcd_id AS RCD_ID,
COUNT(*) AS CNT
FROM @Sample AS s1
WHERE EXISTS ( SELECT *
FROM @Sample AS s2
WHERE s2.rcd_id = s1.rcd_id
AND s2.e_date > s1.s_date
AND s2.s_date < s1.e_date
AND s1.s_date <> s2.s_date
AND s1.e_date <> s2.e_date
)
GROUP BY s1.rcd_id
ORDER BY s1.rcd_id[/code]I have removed "=" in the comparisons.

E 12°55'05.25"
N 56°04'39.16"

cardgunner

326 Posts

Posted - 2008-06-20 : 17:05:23

Thanks Peso.

Sorry for any confusion.

CardGunner

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources