April 10, 2004 at 9:51 pm
I have a table with the following structure:
Membernbr varchar(11)
Service varchar (6)
SubService varchar(4)
Effective Date int
Each member may have several records in this table, 1 record added for any change in Service or SubService.
I need to transform this data into a table with the following structure:
MemberNbr
ChangeType
StartDate
EndDate
so that each member record shows a continuity of service while a member the end date of the prior service should be the beginning of a new service ( or sub service as the case may be, or both )
Can anyone devise a non-cursor manner for performing this for about 120000 records?
I need to transform this
April 12, 2004 at 5:18 am
Does this work ?
SELECT A.Membernbr ,
CASE WHEN A.Service <> B.Service AND A.SubService <> B.SubService THEN 'DOUBLE CHANGE'
WHEN A.Service <> B.Service THEN 'Service CHANGE'
WHEN A.SubService <> B.SubService THEN 'SubService CHANGE'
ELSE 'NO CHANGE' END
, A.EffDate AS STARTDATE , B.EffDate AS ENDDATE
FROM TEST A INNER JOIN TEST B ON A.Membernbr = B.Membernbr
WHERE B.EffDate = ( SELECT MIN(EffDate ) FROM TEST C WHERE A.Membernbr = C.Membernbr AND A.EffDate < C.EffDate )
April 12, 2004 at 12:07 pm
Thank for the assitance. However, I'm not getting the first record back in a member's history if there is more than 1 record in a member's history
April 12, 2004 at 10:30 pm
Can you put up some sample data and expected output? As per my testing data, I am able to get the desried results.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply