delete extra records

  • Hi experts,

    I have a temporary table from which I would like to keep only the rows that have a unique fkprojectid and if there are multiple rows I want the 'secondcontact' if available or the 'firstcontact' when it is not. Below would be an example of what I might have in the temporary table.

    create table #table_A(fkprojectid int, contacttype varchar(500),contactname varchar(500))

    insert into #table_A(427,'firstcontact','Frank')

    insert into #table_A(427,'secondcontact','Sue')

    insert into #table_A(427,'firstcontact','Sam') -- yes, users have created firstcontact records multiple times for the same or different contactnames

    insert into #table_A(568,'firstcontact','Albert)

    The temporary table is the result of a large join I am modifying and one of the new tables being joined in on the projectid has multiple records for all the different contacttypes. The report needs to capture only one contacttype per fkprojectid and it should be the secondcontact unless there is not one available and then the default is to show the firstcontact. If there are multiples of either type of contact I can take the most recent based on datecreated. I really don't know how to limit the result to contain only one record per fkProjectID.

    my actual join to put information into the temporary table is:

    INSERT INTO #header

    SELECT

    fkProjectID,

    contactname,

    contacttype,

    contactphone,

    contactemail,

    --a bunch more stuff from all the tables being joined

    FROM gen_People PE

    LEFT OUTER JOIN gen_CompanyInformation CI

    ON PE.fkProjectID = CI.fkProjectID

    LEFT OUTER JOIN gen_HRInformation HR

    ON PE.pkPeopleID = HR.fkEmployeeID

    AND PE.fkProjectID = HR.fkProjectID

    LEFT OUTER JOIN gen_ClientContactsCC-- new join

    ON PE.fkProjectID = CC.fkProjectID

    WHERE PE.SSN = @SSN

    AND ((CC.ContactType = 'secondcontact') OR (CC.ContactType = 'firstcontact)) -- new

    It is reasonable in the database to have the same @SSN for more than one fkProjectID but I only want one row per fkProjectID.

    How do I go about doing this in SQL Server 2000? Thanks for your help.

    Warm regards,

  • Hi again,

    I found how to eliminate duplicate contacttypes for the same fkProjectID as below, keeping the newest by DateCreated. So now I think I only have to delete the 'firstcontact' if I have a 'secondcontact' for the same fkProjectID. Please advise if I am going about this the right way. I try to break the problem down into pieces.

    DELETE FROM T1

    FROM #header T1, #header T2

    WHERE T1.fkProjectID = T2.fkProjectID

    AND T1.ContactType = T2.ContactType

    AND T1.DateCreated < T2.DateCreated

    Warm regards,

  • Hi again,

    I got it done! Did the same delete a second time only with firstcontact < secondcontact.

    DELETE FROM T1

    FROM #header T1, #header T2

    WHERE T1.fkProjectID = T2.fkProjectID

    AND T1.ContactType < T2.ContactType

    Warm regards,

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

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