August 27, 2008 at 9:06 am
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,
August 27, 2008 at 9:50 am
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,
August 27, 2008 at 1:56 pm
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