5 consecutive month 1

[chaoma]

Hello,

I need help on how to find ID (below) where Code=5 existed for 5 or more consecutive months from 11/2005 to 11/2006 and change to Code=2 immediately following the consecutive month. Here is a sample data:

ID Code Beg_dt End_dt
1 5 20051201 20051231
1 5 20060101 20060131
1 5 20060201 20060531
1 2 20060601 20060831
2 5 20051101 20051130
2 5 20051201 20051231
2 2 20060101 20060331
2 5 20060401 20061130

ID=1 met the criteria because code=5 existed from 12/1/2005 to 5/31/2006 (6month) and changed to code 2 on 6/1/2006.
ID=2 doesn't met the criteria because code=5 existed only from 11/1/2005 to 12/31/2005 prior to code=2.

Here is the code to create the data in SQL 2000:

SELECT 1 AS [ID], 5 AS CODE, '20051201' AS BEG_DT, '20051231' AS EFF_END_DT
INTO MY_TABLE
UNION ALL
SELECT 1, 5, '20060101','20060131'
UNION ALL
SELECT 1, 5, '20060201', '20060531'
UNION ALL
SELECT 1, 2, '20060601', '20060831'
UNION ALL
SELECT 2, 5, '20051101', '20051130'
UNION ALL
SELECT 2, 5, '20051201', '20051231'
UNION ALL
SELECT 2, 2, '20060101', '20060331'
UNION ALL
SELECT 2, 5, '20060401', '20061130'


Thank you for all your assistance.

Chaoma

 

[AlexCuse]

Ok, this is kinda messy and it involves 3 temp tables, but this should do the trick for you. I have been wanting to use this for a while, and finally have found the right need.

SELECT 1 AS [ID], 5 AS CODE, '20051201' AS BEG_DT, '20051231' AS EFF_END_DT
INTO    #MY_TABLE
UNION ALL
SELECT 1, 5, '20060101','20060131'
UNION ALL
SELECT 1, 5, '20060201', '20060531'
UNION ALL
SELECT 1, 2, '20060601', '20060831'
UNION ALL
SELECT 2, 5, '20051101', '20051130'
UNION ALL
SELECT 2, 5, '20051201', '20051231'
UNION ALL
SELECT 2, 2, '20060101', '20060331'
UNION ALL
SELECT 2, 5, '20060401', '20061130'


select identity(int, 1, 1) as [ID2], a.[ID], a.[CODE], a.[BEG_DT]
into #temp
from #MY_TABLE a
left join #MY_TABLE b
on a.[ID] = b.[ID] and a.[BEG_DT] = dateadd(day, -1, b.[EFF_END_DT]) and a.[CODE] = b.[CODE]
where b.[ID] is null
order by a.[ID], a.[BEG_DT]

select identity(int, 1, 1) as [ID2], a.[ID], a.[CODE], a.[EFF_END_DT]
into #temp2
from #MY_TABLE a
left join #MY_TABLE b
on a.[ID] = b.[ID] and a.[EFF_END_DT] = dateadd(day, -1, b.[BEG_DT]) and a.[CODE] = b.[CODE]
where b.[ID] is null
order by a.[ID], a.[EFF_END_DT]


select a.[ID2], a.[ID], a.[CODE], a.[BEG_DT], b.[EFF_END_DT]
into #temp3 
from #temp a
inner join #temp2 b 
on a.[ID2] = b.[ID2] and a.[CODE] = b.[CODE]
where datediff(month, a.[BEG_DT], b.[EFF_END_DT]) >= 5


select a.* from #MY_TABLE a
inner join #temp3 b
on a.[ID] = b.[ID]
where a.[CODE] = 2 
and a.BEG_DT = dateadd(day, 1, b.[EFF_END_DT])

drop table #MY_TABLE
drop table #temp
drop table #temp2
drop table #temp3

If you take out the piece to create the data, and replace all the #MY_TABLE's with your table name, it should get the job done.

I modified a proc used to show all periods of continuous coverage by ID. I placed the output from existing query (after adding a constraint to limit it to continuous coverage of >= 5 months) into #temp3, then joined to #temp3 from the original on ID, looking only for records where CODE was 2, and start date was one day greater than end date.

If you have any specific questions I willtry to answer them. I am having a lot of trouble verbalizing how this one works though.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

ps when your replace all of the #MY_TABLE with your table name, be VERY VERY careful to remove the DROP command. Just to be safe, use this instead:

select identity(int, 1, 1) as [ID2], a.[ID], a.[CODE], a.[BEG_DT]
into #temp
from #MY_TABLE a
left join #MY_TABLE b
on a.[ID] = b.[ID] and a.[BEG_DT] = dateadd(day, -1, b.[EFF_END_DT]) and a.[CODE] = b.[CODE]
where b.[ID] is null
order by a.[ID], a.[BEG_DT]

select identity(int, 1, 1) as [ID2], a.[ID], a.[CODE], a.[EFF_END_DT]
into #temp2
from #MY_TABLE a
left join #MY_TABLE b
on a.[ID] = b.[ID] and a.[EFF_END_DT] = dateadd(day, -1, b.[BEG_DT]) and a.[CODE] = b.[CODE]
where b.[ID] is null
order by a.[ID], a.[EFF_END_DT]


select a.[ID2], a.[ID], a.[CODE], a.[BEG_DT], b.[EFF_END_DT]
into #temp3 
from #temp a
inner join #temp2 b 
on a.[ID2] = b.[ID2] and a.[CODE] = b.[CODE]
where datediff(month, a.[BEG_DT], b.[EFF_END_DT]) >= 5


select a.* from #MY_TABLE a
inner join #temp3 b
on a.[ID] = b.[ID]
where a.[CODE] = 2 
and a.BEG_DT = dateadd(day, 1, b.[EFF_END_DT])

drop table #temp
drop table #temp2
drop table #temp3

Replace the #MY_TABLEs in that and you will be fine. I would hate for you to drop an important table because I needed to set up test data for the query.

Hope it helps,

Alx

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

I thought of something you may want to add to the insert to #temp3 on my way home last night. It is a slight modification to the where clause, so there are no garbage results in there (namely matching start dates with earlier end dates). Replace it with this:

where a.[BEG_DT] < b.[EFF_END_DT]
and datediff(month, a.[BEG_DT], b.[EFF_END_DT]) >= 5

I think this is my last modification (just in time for the weekend )

Again, hope it helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[chaoma]

I guess I forgot to check my message. Thanks for the response. I'll let you know.

 

[AlexCuse]

No problem. Let me know how it turns out (who knows what will turn up when you try it on more than limited sample data)

Alex

Ignorance of certain subjects is a great part of wisdom

 

[chaoma]

Alex,

After a long analyse of your code, I saw what you're trying to do. Your code work very well. Also, I tried to re-write the code as follow. I think it should be right:

SELECT A.[ID],
A.CODE,
MIN(A.BEG_DT) AS BEG_DT_MIN,
MAX(B.EFF_END_DT) AS EFF_END_DT_MAX
INTO #TEMP4
FROM #MY_TABLE A INNER JOIN #MY_TABLE B ON A.[ID]=B.[ID]
WHERE A.CODE=5
AND B.CODE=5
AND DATEDIFF(MONTH, A.BEG_DT, B.EFF_END_DT)>=5
GROUP BY A.[ID], A.CODE

SELECT A.*, B.*
FROM #TEMP4 A INNER JOIN MY_TABLE B ON A.[ID]=B.[ID]
WHERE B.CODE=2
AND B.BEG_DT=DATEADD(DAY,1,A.EFF_END_DT_MAX)


Thank you. Here is a shiny star for you.

 

[AlexCuse]

Hehe it is a tricky one. I read large part of it in an old post by either vongrunt or SQLDenis, and it took me much time to figure out what it was doing.

One thing to consider about your modification is that it works with the sample data, but if there were a gap in coverage it would still show up. If you select everything from #TEMP4, and look at the entry for ID = 2, you will see what I mean when you view the beginning/end date. Just be careful of that when you are using this in production, as it may not always work as you expect.

Take this data for example, with ID = 3 you will see exactly what I mean.

SELECT 1 AS [ID], 5 AS CODE, '20051201' AS BEG_DT, '20051231' AS EFF_END_DT
INTO    #MY_TABLE
UNION ALL
SELECT 1, 5, '20060101','20060131'
UNION ALL
SELECT 1, 5, '20060201', '20060531'
UNION ALL
SELECT 1, 2, '20060601', '20060831'
UNION ALL
SELECT 2, 5, '20051101', '20051130'
UNION ALL
SELECT 2, 5, '20051201', '20051231'
UNION ALL
SELECT 2, 2, '20060101', '20060331'
UNION ALL
SELECT 2, 5, '20060401', '20061130'
UNION ALL
select 3, 5, '20060101', '20060131'
UNION ALL 
select 3, 5, '20061001', '20061031'
UNION ALL 
select 3, 2, '20061101', '20061201'

Glad it worked though!

Alex

Ignorance of certain subjects is a great part of wisdom

 

[chaoma]

Oh, no.

Thank you for pointing that out. I don't want gap.

 

[AlexCuse]

Hehe, it is tempting to try to save lines of code, but dangerous with this one. If your data set is not too large, then the query should not be too slow. You could always try various options including using table variables, indexing your temp tables, and using subqueries instead of creating temp table 3 if you need to make it run faster.



Ignorance of certain subjects is a great part of wisdom