combine recs by date 2

[roswald]

I have a table that will have a start date, end date, and a third field called ID. If when going through this table that is indexed by ID, start Date, I want to take all the fields that have no breaks in the date fields to be combined into 1 record where possible. For example, I have 3 consecutive recs that are arranged as follows...
ID start end
1 1/1/2006 3/15/2006
1 3/16/2006 6/12/2006
1 6/13/2006 7/24/2006
These 3 recs would be combined into 1 rec as follows...
ID start end
1 1/1/2006 7/24/2006

Can someone show me how to do this?

Thanks in advance for your help!

bob

 

[AlexCuse]

This should do the trick, provided your Date columns are date/time formats:

select [ID], min(start) as StartDate, max(end) as EndDate
from TABLE
group by [ID]

Hope it helps,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[roswald]

Alex,
But I will also have recs that aren't in order with no breaks in coverage like this...
ID Start End
1 1/1/2006 2/10/2006
1 2/11/2006 3/12/2006
1 4/1/2006 5/15/2006

This should be made into 2 records, so I need something different than what you sent me to cover these recs. The result should show these 2 recs...
ID Start End
1 1/1/2006 3/12/2006
1 4/1/2006 5/15/2006

Sorry for not being more clear!

bob

 

[AlexCuse]

Is there anything else in the table you can group on, or is there currently no way to tell (by looking just at the record from 4/1 to 5/15) that person 1 had a lapse in coverage and this is his second time being covered?

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[roswald]

Nope! What you see here is all I have to work with.

bob

 

[abaldwin]

I dont think you are going to be able to do this strictly in SQL.

My only thought is that you would need to use VB or some other programming language, and after creating a connection and filling a recordset, loop through the records and upon finding a date that is not +1 from the current end date, stop and insert the records so far into a temp table.

If someone comes up with a pure sql way of solving your request then I for one will surely be interested.



Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[AlexCuse]

[long post]

Ha I was just about to post the same thing. I think you could accomplish it in pure SQL, but using VB is probably a better option. This is how I would approach your problem:

To start I would create an empty table with the exact same schema as yours, except add an incrementing identity field and a field called [CovIndicator] or something. Then insert into your new table something like this:

 select [ID], start, end
from table
order by [ID], start, end

This will get everything in a proper order for you, so that you can loop through records while incrementing a counter. This counter will always be equal to your Identity field in the new table, so you will always be looking at the next record.

So your program will start with record 1, and assign CovIndicator = 1. Then it gets tricky.

You'll want to look at the ID for the next record. If it is different ID, then you again assign CovIndicator =1.

If ID is the same, then you look at the previous row's end date. If [current record's start date] = dateadd(d, 1, [previous record end]) then you again assign CovIndicator = 1.

If [current record's start date] <> dateadd(d, 1, [previous record end]) then you will want to increment CovIndicator to equal [Previous CovIndicator] + 1, in this case 2.

As you can see, this would take some time to write and validate, and would contain several variables (and with them a lot of incrementing to keep track of). It would also run incredibly slowly.

When it is through running, you would have the additional field you want to group on for your query though.

select [ID], CovIndicator, min(start) as StartDate, max(end) as EndDate
from TABLE
group by [ID], CovIndicator

Then you just need to tweak your front end so that when a record is added it looks in your table to see what CovIndicator should be assigned.

I will also be very interested to see your eventual solution, please keep us posted.

Alex

[/long post]





Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[vongrunt]

My SQL skillz are very rusty last few months... anyway, here is one approach:

1. Find all dates where intervals begin
2. Rank these dates
3. Find all dates where intervals end
4. Rank these dates
5. Join 2) and 4) on rank value

I guess ID is partitioning value so... sample data:

create table blah( ID int, start smalldatetime, [end] smalldatetime )
set dateformat mdy

insert into blah values (1, '01/01/2006', '3/15/2006' )
insert into blah values (1, '03/16/2006', '6/12/2006' )
insert into blah values (1, '06/13/2006', '7/24/2006' )

Code:

select identity(int, 1, 1) as seq, T1.ID, T1.start
into #tmp1
from blah T1
left outer join blah T2 on T1.ID = T2.ID and T1.start = T2.[end] + 1
where T2.ID is null 
order by T1.ID, T1.start
-- option (maxdop 1)

select identity(int, 1, 1) as seq, T1.ID, T1.[end]
into #tmp2
from blah T1
left outer join blah T2 on T1.ID = T2.ID and T1.[end] = T2.start - 1
where T2.ID is null 
order by T1.ID, T1.[end]
-- option (maxdop 1)

select T1.ID, T1.start, T2.[end]
from #tmp1 T1
inner join #tmp2 T2 on T1.seq = T2.seq

drop table #tmp1, #tmp2
-- drop table blah

FWIW this is much easier to do with SQL2005 (thanks to ranking functions).

------
chemistry - the only natural science that can be broken down into the categories a) making drugs and b) blowing stuff up

 

[AlexCuse]

I'm impressed vongrunt. Have a

, even though it was not my question. It accomplished pretty much what I wanted to do (albeit temporarily) with MUCH less coding or messing with table schema.

Thanks for the lesson,

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[roswald]

Hey Vongrut,
That is the exact code I was looking for.
I knew it could be done directly in sql but just didn't have the expertise to make it work.
Abaldwin, take a look at his post because that is the solution right there.
Thanks to all of you for helping out!

bob