Help collapsing date spans - 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)

Help collapsing date spans

Author

Topic

c4e4
Starting Member

16 Posts

Posted - 2007-01-02 : 20:55:55

Hi
I would appreciate some assistance with this task. I have a table that contains the following:

member_id provider_id startdate enddate
1555949 1000479 2005-02-03 2005-07-12
1648108 1000479 2004-07-20 2004-08-01
1648108 1000479 2004-08-21 2004-10-28
1648108 1000479 2004-10-31 2005-02-03
1232234 1000479 2004-01-01 2004-01-13
1232234 1000479 2004-01-13 2004-01-20
1566678 1000479 2005-02-03 2005-02-23
1566678 1000479 2005-02-28 2005-03-31
1566678 1000479 2005-03-31 2005-04-15

What I need to do is collapse the date spans by member_id and provider_id so that if the are 2 days or less between the enddate and the next startdate, the startdate will drop and the next enddate will become the new enddate.

Here is what the output should look like for the above data:

member_id provider_id startdate enddate
1555949 1000479 2005-02-03 2005-07-12
1648108 1000479 2004-07-20 2004-08-01
1648108 1000479 2004-08-21 2005-02-03
1232234 1000479 2004-01-01 2004-01-20
1566678 1000479 2005-02-03 2005-02-23
1566678 1000479 2005-02-28 2005-04-15

I would appreciate any assistance with this. I have tried self joining the table but really got more mixed up and finally decided to ask for help.

Thank you.

JLH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-02 : 21:23:38

What about adjactant dates?

1648108 1000479 2004-07-20 2004-08-11
1648108 1000479 2004-08-12 2005-02-03

be considered as

1648108 1000479 2004-07-20 2005-02-03

or as is?

Peter Larsson
Helsingborg, Sweden

c4e4
Starting Member

16 Posts

Posted - 2007-01-02 : 21:27:55

It would be the latter:

1648108 1000479 2004-07-20 2005-02-03

The rule that if there are 2 days or more between the enddate and the next startdate then there would be two records. If the gap between the enddate and the next startdate is 2 days or less then the record should be collapsed into one date span.
THANKS!!

JLH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-02 : 22:13:54

[code]-- prepare sample data
declare @t table (member_id int, provider_id int, startdate datetime, enddate datetime)

insert @t
select 1555949, 1000479, '2005-02-03', '2005-07-12' union all
select 1648108, 1000479, '2004-07-20', '2004-08-01' union all
select 1648108, 1000479, '2004-08-21', '2004-10-28' union all
select 1648108, 1000479, '2004-10-31', '2005-02-03' union all
select 1232234, 1000479, '2004-01-01', '2004-01-13' union all
select 1232234, 1000479, '2004-01-13', '2004-01-20' union all
select 1566678, 1000479, '2005-02-03', '2005-02-23' union all
select 1566678, 1000479, '2005-02-28', '2005-03-31' union all
select 1566678, 1000479, '2005-03-31', '2005-04-15'

select member_id,
provider_id,
startdate,
enddate
from @t
order by member_id,
provider_id,
startdate,
enddate

-- stage data
declare @s table (member_id int, provider_id int, startdate datetime, enddate datetime)

insert @s
select t.member_id,
t.provider_id,
min(t.startdate),
max(t.enddate)
from @t t
cross join @t x
where t.member_id = x.member_id
and t.provider_id = x.provider_id
and t.startdate <> x.startdate
and t.enddate <> x.enddate
and (t.startdate between dateadd(day, -1, x.startdate) and dateadd(day, 1, x.enddate) or t.enddate between dateadd(day, -1, x.startdate) and dateadd(day, 1, x.enddate))
group by t.member_id,
t.provider_id

insert @s
select t.member_id,
t.provider_id,
t.startdate,
t.enddate
from @t t
where not exists ( select null
from @s s
where s.member_id = t.member_id
and s.provider_id = t.provider_id
and (t.startdate between s.startdate and s.enddate or t.enddate between s.startdate and s.enddate)
)

-- show the result
select member_id,
provider_id,
startdate,
enddate
from @s
order by member_id,
provider_id,
startdate,
enddate[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-03 : 04:22:05

Use with caution. I have found a bug...

Peter Larsson
Helsingborg, Sweden

c4e4
Starting Member

16 Posts

Posted - 2007-01-03 : 08:12:06

Thank you Peter. I will use your code and see what results. What is the bug you found.
Thanks again!!!

JLH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-03 : 10:48:09

If same member and provider has multiple, non-sequenced, adjactant data ranges.

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-03 : 11:26:49

What happens when there are 3 dates in a row that end within two days or each other? Does those collapse into 1 date? What if there are 10? Your specifications and sample data do not explain that possibility.

- Jeff

c4e4
Starting Member

16 Posts

Posted - 2007-01-03 : 12:35:08

There may be many records for each member_id and provider_id combination. One member_id may have an unlimited number of date spans for a particular provider_id.

One member_id/provider_id combination could have some records with the same end and start day, some records with adjacent end and start dates,some with 2 days or less gap between end and start dates , and may have records with greater than 2 days gap between end and start dates.

All combinations are possible for each member_id,provider_id combination.

I'm sorry if this is not clearer and I thank you for your responses.

JLH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-03 : 13:18:06

Here is a secure way to accomplish your task.

-- prepare sample data
declare	@t table (member_id int, provider_id int, startdate datetime, enddate datetime)

insert	@t
select	1, 1, '2005-02-03', '2005-07-12' union all
select	1, 1, '2005-01-03', '2005-08-12' union all
select	1, 1, '2005-04-03', '2005-09-12' union all
select	1, 1, '2005-10-03', '2006-07-12' union all
select	1, 1, '2006-08-03', '2006-09-12' union all
select	1, 1, '2006-09-13', '2006-10-12' union all
select	1, 1, '2006-10-14', '2006-12-31' union all
select	1555949, 1000479, '2005-02-03', '2005-07-12' union all
select	1648108, 1000479, '2004-07-20', '2004-08-01' union all
select	1648108, 1000479, '2004-08-21', '2004-10-28' union all
select	1648108, 1000479, '2004-10-31', '2005-02-03' union all
select	1232234, 1000479, '2004-01-01', '2004-01-13' union all
select	1232234, 1000479, '2004-01-13', '2004-01-20' union all
select	1566678, 1000479, '2005-02-03', '2005-02-23' union all
select	1566678, 1000479, '2005-02-28', '2005-03-31' union all
select	1566678, 1000479, '2005-03-31', '2005-04-15'

-- stage data
declare	@s table (rowid int identity(1, 1), member_id int, provider_id int, startdate datetime, enddate datetime)

insert		@s
select		member_id,
		provider_id,
		startdate,
		enddate
from		@t
order by	member_id,
		provider_id,
		startdate,
		enddate

-- work with the data
declare	@currentrowid int,
	@memberid int,
	@providerid int,
	@startdate datetime,
	@enddate datetime

select	@currentrowid = max(rowid)
from	@s

while @currentrowid >= 1
	begin
		select	@memberid = member_id,
			@providerid = provider_id,
			@startdate = startdate,
			@enddate = enddate
		from	@s
		where	rowid = @currentrowid

		update	@s
		set	startdate = case when startdate < @startdate then startdate else @startdate end,
			enddate = case when enddate > @enddate then enddate else @enddate end
		where	member_id = @memberid
			and provider_id = @providerid
			and (@startdate between dateadd(day, -1, startdate) and dateadd(day, 1, enddate) or @enddate between dateadd(day, -1, startdate) and dateadd(day, 1, enddate))

		select	@currentrowid = @currentrowid - 1
	end

-- show the result
select distinct	member_id,
		provider_id,
		startdate,
		enddate
from		@s
order by	member_id,
		provider_id,
		startdate,
		enddate

Peter Larsson
Helsingborg, Sweden

c4e4
Starting Member

16 Posts

Posted - 2007-01-03 : 14:37:07

Great, I will run this with my data and keep my fingers crossed!
Thanks again.

JLH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources