Self Joining Challenge 1

[PGO01]

Here's a challenge for you experts:

I've got 3 tables:

Case
CaseID

CaseApp
CaseAppID
CaseID
Type
Date

CaseAppMember
CaseAppMemberID
CaseAppID

So as you can see from the above a Case has many CaseApps and a CaseApp has many CaseAppMembers.

My problem: I need to consolodate all of the CaseApps with the same Type and the same Date into 1.

This means I need to point all of the CaseAppMembers from the CaseApps that are to be deleted to the CaseApp that I am keeping (the first one).

I don't mind splitting this into 2 or 3 separate queries if it's easier - but can anyone help with this please?

Here's what I've got to show the numbers of CaseApps with the same date and type.

-- Show the number of applications on a case where the type
-- and date are the same.
SELECT
	COUNT(*),
	C.CaseID,
	CA.TypeID,
	CA.Date
FROM
	[Case] C
		INNER JOIN CaseApp CA ON CA.CaseID = C.CaseID
GROUP BY
	C.CaseID,
	CA.TypeID,
	CA.Date
ORDER BY
	COUNT(*) DESC

 

[markros]

if object_ID(tempDb..#tempKeep) IS NOT NULL
  drop table tempDB..#tempKeep

if object_ID(tempDb..#tempToDelete) IS NOT NULL
  drop table tempDB..#tempToDelete

select Min(CaseAppID) as CaseAppID, Type, Date, CaseID into #tempKeep from CaseApp group by CaseID, Type, Date having count(*) > 1

select X.*, T.CaseApp as NewCaseApp into #TempToDelete from CaseApp X INNER JOIN #tempKeep T on
X.CaseID = T.CaseID and ... and X.CaseAppID < T.CaseAppID

update CaseAppMember set CaseApp = K.NewCaseAPP from caseAppMember inner join #TempToDelete K on CaseAppMember.CaseApp = K.CaseApp

delete ...

From the top of my head - just an idea.

 

[PGO01]

Great thanks! The X.CaseID = T.CaseID and ... and X.CaseAppID < T.CaseAppID should have a greater-than not a less-than but good job!

 

[markros]

Correct - I think in my mind I started from the ToKeep first