TSQL HELP NEEDED

  • 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

     

  • 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  )


    -- Amit



    "There is no 'patch' for stupidity."



    Download the Updated SQL Server 2005 Books Online.

  • 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

  • Can you put up some sample data and expected output? As per my testing data, I am able to get the desried results.

     

     


    -- Amit



    "There is no 'patch' for stupidity."



    Download the Updated SQL Server 2005 Books Online.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply