Need help with this one... - 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

Transact-SQL (2000)

Need help with this one...

Author

Topic

nev_sql
Starting Member

3 Posts

Posted - 2006-06-27 : 07:37:09

Hi,

I have the following table

id nr GROUP DATE_BEGIN DATE_END TREATMENT
1 1 3 1-1-2005 15-1-2005 111111
2 1 3 14-1-2005 16-2-2005 222222
3 1 3 14-1-2005 16-2-2005 111111
4 1 3 17-3-2005 18-4-2005 122312
5 1 3 18-5-2005 17-6-2005 333333
6 2 4 17-1-2005 16-2-2005 333333
7 2 4 15-2-2005 18-3-2005 458521
The query should give as result only the rows with a none overlap or double period while nr and group are the same.

So in this example i only want to see row 4 and 5.

Thanx for your time.

Nevil

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 07:48:46

So for the test data present, the wanted output is

id  nr  GROUP  DATE_BEGIN   DATE_END  TREATMENT
4    1      3   17-3-2005  18-4-2005     122312
5    1      3   18-5-2005  17-6-2005     333333

Ryan, are you up for it?

Peter Larsson
Helsingborg, Sweden

nev_sql
Starting Member

3 Posts

Posted - 2006-06-27 : 07:58:52

Yes that is correct.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 08:56:47

Hey, it works!

-- Prepare test data
declare @test table ([id] int, [nr] int, [GROUP] int, [DATE_BEGIN] datetime, [DATE_END] datetime, [TREATMENT] int)

set dateformat dmy

insert @test
select 1, 1, 3,  '1-1-2005', '15-1-2005', 111111 union all
select 2, 1, 3, '14-1-2005', '16-2-2005', 222222 union all
select 3, 1, 3, '14-1-2005', '16-2-2005', 111111 union all
select 4, 1, 3, '17-3-2005', '18-4-2005', 122312 union all
select 5, 1, 3, '18-5-2005', '17-6-2005', 333333 union all
select 6, 2, 4, '17-1-2005', '16-2-2005', 333333 union all
select 7, 2, 4, '15-2-2005', '18-3-2005', 458521

-- Select the items
select distinct t2.*
from		@test t1
inner join	@test t2 on t2.nr = t1.nr and t2.[group] = t1.[group]
where		t1.date_end < t2.date_begin

Peter Larsson
Helsingborg, Sweden

nev_sql
Starting Member

3 Posts

Posted - 2006-06-27 : 09:33:53

It is working! thanx for your time!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 09:50:59

quote:
Originally posted by nev_sql

It is working! thanx for your time!

What about date intervals

203 18-1-2006  22-2-2006
708 22-2-2006  6-3-2006

Is that allowed? Should both rows 203 and 708 be output?

If consecutive date intervals are treated as overlapping, use this alternative code

SELECT	*
FROM 	@test
WHERE 	id NOT IN	(
				SELECT		t1.id
				FROM		@test t1
				INNER JOIN	@test t2 ON t2.nr = t1.nr AND t2.[group] = t1.[group] AND t1.id <> t2.id
				WHERE		(t1.date_begin >= t2.date_begin AND t1.date_begin <= t2.date_end)
						or (t1.date_end >= t2.date_begin AND t1.date_end <= t2.date_end)
			)

If consecutive date intervals are treated as non-overlapping, use this code

SELECT	*
FROM 	@test
WHERE 	id NOT IN	(
				SELECT		t1.id
				FROM		@test t1
				INNER JOIN	@test t2 ON t2.nr = t1.nr AND t2.[group] = t1.[group] AND t1.id <> t2.id
				WHERE		(t1.date_begin > t2.date_begin AND t1.date_begin < t2.date_end)
						or (t1.date_end > t2.date_begin AND t1.date_end < t2.date_end)
			)

Try the different outputs with this test data

declare @test table ([id] int, [nr] int, [GROUP] int, [DATE_BEGIN] datetime, [DATE_END] datetime, [TREATMENT] int)

set dateformat dmy

insert @test
select 1, 1, 3,  '1-1-2005', '15-1-2005', 111111 union all
select 2, 1, 3, '14-1-2005', '16-2-2005', 222222 union all
select 3, 1, 3, '14-1-2005', '16-2-2005', 111111 union all
select 4, 1, 3, '17-3-2005', '18-4-2005', 122312 union all
select 5, 1, 3, '18-5-2005', '17-6-2005', 333333 union all
select 6, 2, 4, '17-1-2005', '16-2-2005', 333333 union all
select 7, 2, 4, '15-2-2005', '18-3-2005', 458521 union all
select 8, 1, 5, '18-5-2005', '17-6-2005', 333333 union all
select 9, 1, 5, '17-6-2005', '16-7-2005', 333333

Peter Larsson
Helsingborg, Sweden

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-27 : 12:58:40

quote:
Originally posted by Peso

So for the test data present, the wanted output is
id nr GROUP DATE_BEGIN DATE_END TREATMENT
4 1 3 17-3-2005 18-4-2005 122312
5 1 3 18-5-2005 17-6-2005 333333
Ryan, are you up for it?

Peter Larsson
Helsingborg, Sweden

I am too slow

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-27 : 13:17:36

Is this the same thing?

--data
declare @test table ([id] int, [nr] int, [GROUP] int, [DATE_BEGIN] datetime, [DATE_END] datetime, [TREATMENT] int)

set dateformat dmy
insert @test
select 1, 1, 3,  '1-1-2005', '15-1-2005', 111111 union all
select 2, 1, 3, '14-1-2005', '16-2-2005', 222222 union all
select 3, 1, 3, '14-1-2005', '16-2-2005', 111111 union all
select 4, 1, 3, '17-3-2005', '18-4-2005', 122312 union all
select 5, 1, 3, '18-5-2005', '17-6-2005', 333333 union all
select 6, 2, 4, '17-1-2005', '16-2-2005', 333333 union all
select 7, 2, 4, '15-2-2005', '18-3-2005', 458521 union all
select 8, 1, 5, '18-5-2005', '17-6-2005', 333333 union all
select 9, 1, 5, '17-6-2005', '16-7-2005', 333333

--calculation
select * from @test a
where not exists
    (select * from @test
     where nr = a.nr and a.[group] = [group] and not id = a.id
       and not (date_begin > a.date_end or date_end < a.date_begin))

/*results
id          nr          GROUP       DATE_BEGIN                                             DATE_END                                               TREATMENT   
----------- ----------- ----------- ------------------------------------------------------ ------------------------------------------------------ ----------- 
4           1           3           2005-03-17 00:00:00.000                                2005-04-18 00:00:00.000                                122312
5           1           3           2005-05-18 00:00:00.000                                2005-06-17 00:00:00.000                                333333
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-27 : 13:32:28

[code]"(t1.date_begin >= t2.date_begin AND t1.date_begin <= t2.date_end)
or (t1.date_end >= t2.date_begin AND t1.date_end <= t2.date_end)"[/code]

To find an overlap you only need to check:
[code]t1.date_begin <= t2.date_end AND t1.date_end >= t2.date_begin[/code]

rockmoose

Edit:
As Ryan just said......

Another rewrite:
[code]SELECT t1.*
FROM @test t1
LEFT JOIN @test t2 ON t2.nr = t1.nr AND t2.[group] = t1.[group] AND t1.id <> t2.id
AND t1.date_begin <= t2.date_end AND t1.date_end >= t2.date_begin
WHERE t2.Id IS NULL[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources