Print ''
Print '-------------------------------------------------------------------------'
Print ' Script for JobMgr:'
Print ' Autor        : J.Feuerstein'
Print ' Description  : List Irs having N altreps                                '
Print '-------------------------------------------------------------------------'
GO

use [#DB_N#]
GO

select * from (  
SELECT 
  IR.ObjectFromSiteID AS IrFromSite,
  IR.ObjectName AS IrID,
--  DS.ObjectType,
  count(*) AS HasN_UGALTREP
FROM
  dbo.Objects_tbl IR
  INNER JOIN dbo.Objects_Ir_Rel_tbl ON (IR.Id = dbo.Objects_Ir_Rel_tbl.IrId)
  INNER JOIN dbo.Objects_tbl DS ON (dbo.Objects_Ir_Rel_tbl.ObjectId = DS.Id)
WHERE
  -- IR.ObjectFromSiteID = 'tc83' AND
  DS.ObjectType = 'UGALTREP'
--  DS.ObjectType = 'UGPART'
GROUP BY
  IR.ObjectFromSiteID,
  IR.ObjectName,
  DS.ObjectType  
) as xx
where 
 HasN_UGALTREP > 20
 order by IrFromSite, HasN_UGALTREP desc
