MERGE INTO dbo.Objects_tbl AS OBJ

USING (
    SELECT DS_IR.DS_DbId, DsCountsByType.IrContainsIdeasMaster, DsCountsByType.IrContainsIdeasPart,
	                      DsCountsByType.IrHasMissingUGMaster, DsCountsByType.IrHasMissingUGPart

    FROM (
		SELECT -- IR Identification
			   IrId, IrName,

			   -- counts of objects by type
			   IdeasPartOrAssembly_Count, UGMaster_Count, IdeasDrawing_Count, UGPart_Count,

			   -- which ideas element is available
			   CASE WHEN IdeasPartOrAssembly_Count > 0 THEN 1 ELSE 0 END AS IrContainsIdeasMaster,
			   CASE WHEN IdeasDrawing_Count > 0        THEN 1 ELSE 0 END AS IrContainsIdeasPart,

			   -- which element is missing
			   CASE WHEN IdeasPartOrAssembly_Count > UGMaster_Count THEN 1 ELSE 0 END AS IrHasMissingUGMaster,
			   CASE WHEN IdeasDrawing_Count > UGPart_Count          THEN 1 ELSE 0 END AS IrHasMissingUGPart

		FROM (

			  -- count objects by type of each IR
			  SELECT IR.Id AS IrId, IR.IrName,
					 SUM(CASE WHEN DS.ObjectType IN (N'IDEASPART', N'IDEASASSEMBLY') THEN 1 ELSE 0 END) AS IdeasPartOrAssembly_Count,
					 SUM(CASE WHEN DS.ObjectType = N'UGMASTER'     THEN 1 ELSE 0 END) AS UGMaster_Count,
					 SUM(CASE WHEN DS.ObjectType = N'IDEASDRAWING' THEN 1 ELSE 0 END) AS IdeasDrawing_Count,
					 SUM(CASE WHEN DS.ObjectType = N'UGPART'       THEN 1 ELSE 0 END) AS UGPart_Count

			  FROM dbo.Objects_tbl    AS DS    WITH (nolock)
			  JOIN dbo.DS_IR_rel_View AS DS_IR WITH (nolock) ON DS_IR.DS_DbId=DS.Id
			  JOIN dbo.IRs_View       AS IR    WITH (nolock) ON IR.Id = DS_IR.IR_DbId

			  GROUP BY IR.Id, IR.IrName

		    ) AS GroupByQuery
         ) AS DsCountsByType

	JOIN dbo.DS_IR_rel_View AS DS_IR ON DS_IR.IR_DbId = DsCountsByType.IrId

	-- this grouping prevents issues with duplicate IR-DS relations
	GROUP BY DS_IR.DS_DbId, DsCountsByType.IrContainsIdeasMaster, DsCountsByType.IrContainsIdeasPart,
	                        DsCountsByType.IrHasMissingUGMaster, DsCountsByType.IrHasMissingUGPart

) AS Q

ON OBJ.Id = Q.DS_DbId

WHEN MATCHED
    THEN UPDATE
	     SET ProcStateCol10 =
	   CASE

				 -- DS is Master
				 WHEN OBJ.ObjectType IN (N'IDEASPART', N'IDEASASSEMBLY', N'UGMASTER') THEN
				      CASE
					      WHEN Q.IrContainsIdeasMaster=1 AND Q.IrHasMissingUGMaster=0 THEN N'D.NxMaster.OK'
					      WHEN Q.IrContainsIdeasMaster=1 AND Q.IrHasMissingUGMaster=1 THEN N'D.NxMaster.ERR'
						  WHEN OBJ.ObjectType=N'UGMASTER' THEN N'D.NXOnly.OK'
						  ELSE N'D.NoMatch01.WRN' -- dieser ELSE-Fall sollte eigentlich nie auftreten
					  END

				 -- DS is Non-Master
				 WHEN OBJ.ObjectType IN (N'IDEASDRAWING', N'UGPART') THEN
				      CASE
					      WHEN Q.IrContainsIdeasPart=1 AND Q.IrHasMissingUGPart=0 THEN N'D.NxNonMaster.OK'
					      WHEN Q.IrContainsIdeasPart=1 AND Q.IrHasMissingUGPart=1 THEN N'D.NxNonMaster.ERR'
						  WHEN OBJ.ObjectType=N'UGPART' THEN N'D.NXOnly.OK'
						  ELSE N'D.NoMatch02.WRN' -- dieser ELSE-Fall sollte eigentlich nie auftreten
					  END

				 ELSE N'D.UnknownObjectType.WRN'
			 END

WHEN NOT MATCHED BY SOURCE
	THEN UPDATE
	     SET ProcStateCol10 = CASE WHEN OBJ.ObjectClass = N'DS' THEN N'D.NXOnly.OK' ELSE N'D.NoDS.OK' END

;

