Overlapping Start Date Sql Server 2000 help

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)

Overlapping Start Date Sql Server 2000 help

Author

Topic

Husman
Starting Member

13 Posts

Posted - 2007-04-04 : 06:58:21

HI Guys,

I'm new to sql. Can someone help me to write a script to select overlapping start dates for each client records.
For example:
Clientid 1 have 3 episode as below(I only want to see the first two records with overlapping start date records)

clientid StratDate EndDate
1 2004-01-01 2004-05-01
1 2004-04-01 2004-05-01
1 2005-04-01 2006-01-01

Table create

CREATE TABLE [dbo].[TABLE_TEST] (
[Client_ID] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[STARTDate] [datetime] NULL ,
[ENDDate] [datetime] NULL ,
)
GO

INSERT

INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('1','2004-01-01','2004-05-01')

INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('1','2004-04-01','2004-05-01')

INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('1','2005-04-01','2006-04-01')

INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('2','2004-06-01','2004-07-01')

INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('3','2004-09-01','2004-010-01')
Go

Thanks for help

Husman

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-04 : 07:11:10

[code]declare @t TABLE
(
[Client_ID] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[STARTDate] [datetime] NULL ,
[ENDDate] [datetime] NULL
)

INSERT INTO @t([Client_ID], [STARTDate], [ENDDate])
VALUES('1','2004-01-01','2004-05-01')

INSERT INTO @t ([Client_ID], [STARTDate], [ENDDate])
VALUES('1','2004-04-01','2004-05-01')

INSERT INTO @t ([Client_ID], [STARTDate], [ENDDate])
VALUES('1','2005-04-01','2006-04-01')

INSERT INTO @t ([Client_ID], [STARTDate], [ENDDate])
VALUES('2','2004-06-01','2004-07-01')

INSERT INTO @t ([Client_ID], [STARTDate], [ENDDate])
VALUES('3','2004-09-01','2004-010-01')

select top 2 t1.* from @t t1
Join
(select client_id from @t group by client_id having count(startdate) > 1) t2
on t1.Client_ID = t2.Client_ID
order by t1.startdate[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Husman
Starting Member

13 Posts

Posted - 2007-04-04 : 07:24:59

Thanks for reply . It did't work. I want query to be flexable. Will work if use top 2

select t1.* from TABLE_TEST t1
Join
(select client_ID from TABLE_TEST group by client_ID having count(startdate) > 1) t2
on t1.client_ID = t2.Trust_Wide_ID
order by t1.startdate

NeilG
Aged Yak Warrior

530 Posts

Posted - 2007-04-04 : 07:34:27

do you mean will it work without the top 2 then the answer is try and see

Husman
Starting Member

13 Posts

Posted - 2007-04-04 : 07:39:23

Only work with top 2.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-04 : 07:59:20

What's wrong with TOP 2? Since you said that you wanted first two overlapping records, that is what you are getting using TOP 2, isn't it?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Husman
Starting Member

13 Posts

Posted - 2007-04-04 : 09:14:25

Top 2 was just a example.There are many clients with overlapping start date.they could be in any order.

Husman
Starting Member

13 Posts

Posted - 2007-04-04 : 09:14:57

Top 2 was just a example.There are many clients with overlapping start date.they could be in any order.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-04 : 09:16:09

quote:
Originally posted by Husman

Top 2 was just a example.There are many clients with overlapping start date.they could be in any order.

then you better post more sample data that will illustrate your point here. Don't forget the expected result.

Husman
Starting Member

13 Posts

Posted - 2007-04-04 : 10:05:10

Thanks for help.
I have writing a script which check for overlapping.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources