December 21, 2005 at 5:22 pm
I have a table which has the following structure:
MemberNbr varchar(11)
Status1 Char(1)
Status2 Char(1)
Status3 Char(1)
EffectiveDate int
Sample Records would be thus:
MemberNbr Status1 Status2 Status3 EffectiveDate
1234567 Y Y 20050101
1234567 Y 20050201
1234567 Y 20050301
1234567 Y Y 20050401
1234567 Y Y Y 20050501
I need to reduce this table so that for each status code I have a start and
end date for each non-contiguous period:
Thus
MemberNbr StatusCode StartDate EndDate
1234567 Status1 20050101 20050301
1234567 Status1 20050401 0
1234567 Status2 20050401 0
1234567 Status3 20050101 20050201
1234567 Status3 20050301 20050401
1234567 Status3 20050501 0
Any help in formulating an SQL Statement would be greatly appreciated.
December 21, 2005 at 7:19 pm
Can you describe what query suppose to return in plain English?
_____________
Code for TallyGenerator
December 21, 2005 at 8:24 pm
This is a variation on the problem of finding contigous theater seats. Search for CELKO and "theater seats" for some other solutions.
The solution is rather long. First you will need an auxiliary table of numbers from 0 thru 32767:
See "Why should I consider using an auxiliary numbers table?" at http://www.aspfaq.com/show.asp?id=2516
use master
go
set nocount on
set xact_abort on
create table Sequences
( Seq smallint not null
, constraint Sequences_PK primary key (Seq) )
-- Create a new table to turn columns into rows or rows into columns
declare @SmallIntMaxinteger
,@SeqMaxinteger
set@SmallIntMax= power(2,15) - 1
-- Populate the Sequences table:
-- zero is also useful.
Insert into Sequences (Seq) values (0)
Insert into Sequences (Seq) values (1)
-- Now repeat the following insert 10 times to get 1024 sequence Sequences
set @SeqMax = 1
while @SeqMax MemberStatusOverlaps.EffectiveDate
AND#MemberStatusWoGaps.EffectiveDate <= DATEADD(mm,ContigousMonths , MemberStatusOverlaps.EffectiveDate)
)
SQL = Scarcely Qualifies as a Language
December 22, 2005 at 2:32 am
You could also try the following:
-- Create test data
declare @MemberStatus table
(
MemberNbr integer not null,
EffectiveDate int not null,
Status1 char(1) not null,
Status2 char(2) not null,
Status3 char(2) not null
)
insert into @MemberStatus
(MemberNbr ,Status1 ,Status2 ,Status3,EffectiveDate)
select 1234567 , 'Y' , ' ' , 'Y' , '20050101' union all
select 1234567 , 'Y' , ' ' , ' ' , '20050201' union all
select 1234567 , ' ' , ' ' , 'Y' , '20050301' union all
select 1234567 , 'Y' , 'Y' , ' ' , '20050401' union all
select 1234567 , 'Y' , 'Y' , 'Y' , '20050501'
-- Create a new table with the same data and an identity column
declare @MemberStatusID table
(
ID int identity(1, 1) primary key,
MemberNbr integer not null,
EffectiveDate int not null,
Status1 char(1) not null,
Status2 char(2) not null,
Status3 char(2) not null
)
insert @MemberStatusID (MemberNbr, EffectiveDate, Status1, Status2, Status3)
select MemberNbr, EffectiveDate, Status1, Status2, Status3 from @MemberStatus
-- Run query
select m1.MemberNbr as MemberNbr, 'Status1' as StatusCode, m1.EffectiveDate as StartDate,
(select isnull(min(m3.EffectiveDate), 0) from @MemberStatusID m3 where m3.ID > m1.ID and m3.Status1 != 'Y')
as EndDate
from
@MemberStatusID m1 left join @MemberStatusID m2
on m2.MemberNbr = m1.MemberNbr and m2.ID = m1.ID - 1
where m1.Status1 = 'Y' and isnull(m2.Status1, '') != 'Y'
UNION ALL
select m1.MemberNbr, 'Status2', m1.EffectiveDate,
(select isnull(min(m3.EffectiveDate), 0) from @MemberStatusID m3 where m3.ID > m1.ID and m3.Status2 != 'Y')
from
@MemberStatusID m1 left join @MemberStatusID m2
on m2.MemberNbr = m1.MemberNbr and m2.ID = m1.ID - 1
where m1.Status2 = 'Y' and isnull(m2.Status2, '') != 'Y'
UNION ALL
select m1.MemberNbr, 'Status3', m1.EffectiveDate,
(select isnull(min(m3.EffectiveDate), 0) from @MemberStatusID m3 where m3.ID > m1.ID and m3.Status3 != 'Y')
from
@MemberStatusID m1 left join @MemberStatusID m2
on m2.MemberNbr = m1.MemberNbr and m2.ID = m1.ID - 1
where m1.Status3 = 'Y' and isnull(m2.Status3, '') != 'Y'
ORDER BY MemberNbr, StatusCode, StartDate
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply