-- Dev
SELECT * INTO #VersionedContentDelete FROM [ConquestDevelopment].[dbo].[VERSIONED_CONTENT]  vcd
-- Remove versioned content that is the latest versioned content
WHERE NOT EXISTS (SELECT * FROM [ConquestDevelopment].[dbo].VersionedContentLatest vcld WITH(NOLOCK) WHERE vcd.CONTENT_ID  = vcld.CONTENT_ID AND vcd.REVISION = vcld.REVISION AND vcd.VERSION_ID = vcld.VERSION_ID)
-- Remove old versioned content that is still in use
AND NOT EXISTS(SELECT * FROM [ConquestDevelopment].[dbo].INVENTORY_GAME igd INNER JOIN [ConquestDevelopment].[dbo].INVENTORY id ON id.ID = igd.ID WHERE id.ITEM_ID = vcd.CONTENT_ID AND igd.VERSION_ID=vcd.VERSION_ID AND igd.REVISION = vcd.REVISION)

-- PROD
INTERSECT
SELECT *  FROM [ConquestProduction].[dbo].[VERSIONED_CONTENT] vcp
-- Remove versioned content that is the latest versioned content
WHERE NOT EXISTS (SELECT * FROM [ConquestProduction].[dbo].VersionedContentLatest vclp WITH(NOLOCK) WHERE vcp.CONTENT_ID  = vclp.CONTENT_ID AND vcp.REVISION = vclp.REVISION AND vcp.VERSION_ID = vclp.VERSION_ID)
-- Remove old versioned content that is still in use
AND NOT EXISTS(SELECT * FROM [ConquestProduction].[dbo].INVENTORY_GAME igp INNER JOIN [ConquestProduction].[dbo].INVENTORY ip ON ip.ID = igp.ID WHERE ip.ITEM_ID = vcp.CONTENT_ID AND igp.VERSION_ID=vcp.VERSION_ID AND igp.REVISION = vcp.REVISION)

-- Test
INTERSECT
SELECT *  FROM [ConquestTest].[dbo].[VERSIONED_CONTENT] vct
-- Remove versioned content that is the latest versioned content
WHERE NOT EXISTS (SELECT * FROM [ConquestTest].[dbo].VersionedContentLatest vclt WITH(NOLOCK) WHERE vct.CONTENT_ID  = vclt.CONTENT_ID AND vct.REVISION = vclt.REVISION AND vct.VERSION_ID = vclt.VERSION_ID)
-- Remove old versioned content that is still in use
AND NOT EXISTS(SELECT * FROM [ConquestTest].[dbo].INVENTORY_GAME igt INNER JOIN [ConquestTest].[dbo].INVENTORY it ON it.ID = igt.ID WHERE it.ITEM_ID = vct.CONTENT_ID AND igt.VERSION_ID=vct.VERSION_ID AND igt.REVISION = vct.REVISION)

DELETE vc2c FROM VERSIONED_CONTENTS_2_CONTENTS vc2c INNER JOIN #VersionedContentDelete ON (vc2c.VERSION_ID = #VersionedContentDelete.VERSION_ID AND vc2c.CONTENT_ID = #VersionedContentDelete.CONTENT_ID AND vc2c.REVISION = #VersionedContentDelete.REVISION)