-- WARNING. DO NOT RUN THIS SCRIPT UNLESS YOU KNOW WHAT YOU ARE DOING.
-- BACK UP YOUR DATABASE FIRST AND CHANGE THE @Cutoff variable as appropriate
begin
  declare
    @Cutoff DateTime
  
  set @Cutoff = '2010-01-01'
  
  create table #temp (pid int, brandid int)
 
  insert into #temp
  (pid, brandid)
  select pb.PID, pb.BrandID from PartBrand pb
  where exists (select * from PartBrandHistory pbh
  where MaintType = 'D'
  and pbh.PID = pb.PID
  and pbh.BrandID = pb.BrandID
  and pbh.MaintDate < @Cutoff
  and not exists (select * from PartBrandHistory pbh0
  where pbh0.PID = pbh.PID
  and pbh0.BrandID = pbh.BrandID
  and pbh0.MaintDate > pbh.MaintDate))

  -- Description
  delete from DescriptionHistory
  where exists (select * from #temp t
  where DescriptionHistory.PID = t.PID
  and DescriptionHistory.BrandID = t.BrandID)
  
  delete from Description
  where exists (select * from #temp t
  where Description.PID = t.PID
  and Description.BrandID = t.BrandID)
  
  -- Pricing
   delete from PriceHistory
  where exists (select * from #temp t
  where PriceHistory.PID = t.PID
  and PriceHistory.BrandID = t.BrandID)
  
  delete from Price
  where exists (select * from #temp t
  where Price.PID = t.PID
  and Price.BrandID = t.BrandID)
  
  -- HazardousMaterial
  delete from HazardousMaterialHistory
  where exists (select * from #temp t
  where HazardousMaterialHistory.PID = t.PID
  and HazardousMaterialHistory.BrandID = t.BrandID)
  
  delete from HazardousMaterial
  where exists (select * from #temp t
  where HazardousMaterial.PID = t.PID
  and HazardousMaterial.BrandID = t.BrandID)
  
  -- Package
  delete from PackageHistory
  where exists (select * from #temp t
  where PackageHistory.PID = t.PID
  and PackageHistory.BrandID = t.BrandID)
  
  delete from Package
  where exists (select * from #temp t
  where Package.PID = t.PID
  and Package.BrandID = t.BrandID)
  
  -- EXPI
  delete from ExpiHistory
  where exists (select * from #temp t
  where ExpiHistory.PID = t.PID
  and ExpiHistory.BrandID = t.BrandID)
  
  delete from Expi
  where exists (select * from #temp t
  where Expi.PID = t.PID
  and Expi.BrandID = t.BrandID)
  
  -- DigitalAsset
  delete from DigitalAssetHistory
  where exists (select * from #temp t
  where DigitalAssetHistory.PID = t.PID
  and DigitalAssetHistory.BrandID = t.BrandID)
  
  delete from DigitalAsset
  where exists (select * from #temp t
  where DigitalAsset.PID = t.PID
  and DigitalAsset.BrandID = t.BrandID)
  
  -- PartBrand
  delete from PartBrandHistory
  where exists (select * from #temp t
  where PartBrandHistory.PID = t.PID
  and PartBrandHistory.BrandID = t.BrandID)
  
  delete from PartBrand
  where exists (select * from #temp t
  where PartBrand.PID = t.PID
  and PartBrand.BrandID = t.BrandID)
  
  -- remove from temp items that still exist in part brand
  delete from #temp
  where exists (select * from PartBrand pb
  where #temp.pid = pb.PID)
  
  -- Interchange
  delete from InterchangeHistory
  where PID in (select PID from #temp)
  
  delete from Interchange
  where PID in (select PID from #temp)
  
  -- Kit
  delete from KitHistory
  where PID in (select PID from #temp)
  
  delete from Kit
  where PID in (select PID from #temp)
  
  -- PartAttribute
  delete from PartAttributeHistory
  where PID in (select PID from #temp)
  
  delete from PartAttribute
  where PID in (select PID from #temp)
  
  -- PartMaster
  delete from PartMasterHistory
  where PID in (select PID from #temp)
  
  delete from PartMaster
  where PID in (select PID from #temp)
  
  drop table #temp
end