/* selectiert alle Datasets die in zur gleichen IR eines Datasets gehören */

-- alle erforderlichen Temptabellen löschen
IF OBJECT_ID ('zztmpPlistDel_onIr_Datasets_tbl', N'U') IS NOT NULL Drop table zztmpPlistDel_onIr_Datasets_tbl
IF OBJECT_ID ('zztmpPlistDel_onIr_Datasets2_tbl', N'U') IS NOT NULL Drop table zztmpPlistDel_onIr_Datasets2_tbl

-- Schritt: Dataset selectieren
SELECT TOP 1 *
  INTO zztmpPlistDel_onIr_Datasets_tbl
FROM
  dbo.Objects_tbl
WHERE
  Objects_tbl.ProcStateCol01 = 'M' AND
  Objects_tbl.ObjectClass = 'DS' AND
  -- Objects_tbl.ObjectFromSiteID = 'im9t' AND
  Objects_tbl.ObjectOwningSiteID = Objects_tbl.ObjectFromSiteID 
  -- AND  Objects_tbl.ObjectUgMaster_CompCount <= 6000
ORDER BY Objects_tbl.ObjectUgMaster_APLevels ASC,
 Objects_tbl.ObjectUgMaster_CompCount ASC,
 Objects_tbl.ObjectLastModDate DESC

-- Schritt: alle Dataset selectieren die zur Ir des Datasets gehoeren und noch nicht verarbeitet wurden !
SELECT
  dbo.Objects_tbl.*
  INTO zztmpPlistDel_onIr_Datasets2_tbl
FROM
  dbo.Objects_Ir_Rel_tbl
  INNER JOIN dbo.Objects_Ir_Rel_tbl Objects_Ir_Rel_tbl1 ON (dbo.Objects_Ir_Rel_tbl.IrId = Objects_Ir_Rel_tbl1.IrId)
  INNER JOIN dbo.Objects_tbl ON (Objects_Ir_Rel_tbl1.ObjectId = dbo.Objects_tbl.Id),
  dbo.zztmpPlistDel_onIr_Datasets_tbl
WHERE
  dbo.Objects_Ir_Rel_tbl.ObjectId = dbo.zztmpPlistDel_onIr_Datasets_tbl.Id AND
  (dbo.Objects_tbl.ProcStateCol01 = 'M' OR
  dbo.Objects_tbl.ProcStateCol01 = 'X')
ORDER BY
  dbo.Objects_tbl.ObjectType DESC
  

-- Schritt Ausgabe des Ergebnisses
select * from zztmpPlistDel_onIr_Datasets2_tbl