Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Procedures    : [JobMgr_30_ApplProceduresObjects_ObjectsUpdate_ProcessStatus_Mark_LastRes]'
Print ' Creation Date : 10.05.2013'
Print ' Autor         : J.Feuerstein'
Print '-------------------------------------------------------------------------'
GO

use [#DB_N#]
GO

Print '-- [ObjectsUpdate_ProcessStatus_Mark_LastRes] ---------------------------'
If Exists (select Name
           from sysobjects
           where Name = "ObjectsUpdate_ProcessStatus_Mark_LastRes" and type = "P")
Drop Procedure ObjectsUpdate_ProcessStatus_Mark_LastRes
GO



CREATE Procedure ObjectsUpdate_ProcessStatus_Mark_LastRes
                 @Objects_tbl_ID  nvarchar(36) ,  -- ID des zu bearbeitenden Datensatzes in JobControl_ObjectsProcessData_tbl
                 @ProcessProcStateCol nvarchar (32)

/* Makiert den LastRes Datensatz in dbo.JobControl_ObjectsProcessData_tbl  erstellt 23.02.2009 J.Fes */
As
BEGIN

  -- EXECUTE zzzzzDebugTbl_Empty
  -- EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus_Mark_LastRes' , '01-@Objects_tbl_ID:' ,  @Objects_tbl_ID
          -- declare  @ObjectId_tmp nvarchar(64)
          -- set @ObjectId_tmp=CONVERT(nvarchar(64), @ObjectId)
          -- EXECUTE zzzzzDebugTbl_AddDataSet 'ObjectsUpdate_ProcessStatus' , '20-@ObjectId:' ,  @ObjectId_tmp

  -- #Update 10.05.2013 08:20 ->   @ProcessProcStateCol .. Optimierung !!!


    with res_tbl (ID, Row_isLastRes_r)
    as
     (SELECT
        dbo.JobControl_ObjectsProcessData_tbl.ID
      ,  CASE (ROW_NUMBER() over (partition by ProcessProcStateCol order by ProcessTimeStart desc))
                               WHEN 1 THEN 1 ELSE 0 END as [Row_isLastRes_r]
      FROM
        dbo.JobControl_ObjectsProcessData_tbl with (nolock)
      WHERE
              dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID = @Objects_tbl_ID
           and dbo.JobControl_ObjectsProcessData_tbl.ProcessProcStateCol = @ProcessProcStateCol
      )

   UPDATE dbo.JobControl_ObjectsProcessData_tbl
       SET dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes = res_tbl.Row_isLastRes_r
        FROM dbo.JobControl_ObjectsProcessData_tbl
        INNER JOIN res_tbl
                ON res_tbl.ID = dbo.JobControl_ObjectsProcessData_tbl.ID


END
GO


Print '-- [ObjectsUpdate_ProcessStatus_Mark_LastRes_ForAll_ProcessGroups] ------'
If Exists (select Name
           from sysobjects
           where Name = "ObjectsUpdate_ProcessStatus_Mark_LastRes_ForAll_ProcessGroups" and type = "P")
Drop Procedure ObjectsUpdate_ProcessStatus_Mark_LastRes_ForAll_ProcessGroups
GO



CREATE Procedure ObjectsUpdate_ProcessStatus_Mark_LastRes_ForAll_ProcessGroups
                 @Objects_tbl_ID  nvarchar(36) ,  -- ID des zu bearbeitenden Datensatzes in JobControl_ObjectsProcessData_tbl
                 @ProcessProcStateCol nvarchar (32)

/* Makiert den LastRes Datensatz in dbo.JobControl_ObjectsProcessData_tbl  erstellt 23.02.2009 J.Fes */
As
BEGIN
    with res_tbl (ID, Row_isLastRes_r)
    as
     (SELECT
        dbo.JobControl_ObjectsProcessData_tbl.ID
      ,  CASE (ROW_NUMBER() over (partition by ProcessProcStateCol order by ProcessTimeStart desc))
                               WHEN 1 THEN 1 ELSE 0 END as [Row_isLastRes_r]
      FROM
        dbo.JobControl_ObjectsProcessData_tbl with (nolock)
      WHERE
              dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID = @Objects_tbl_ID
--           and dbo.JobControl_ObjectsProcessData_tbl.ProcessProcStateCol = @ProcessProcStateCol
      )

   UPDATE dbo.JobControl_ObjectsProcessData_tbl
       SET dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes = res_tbl.Row_isLastRes_r
        FROM dbo.JobControl_ObjectsProcessData_tbl
        INNER JOIN res_tbl
                ON res_tbl.ID = dbo.JobControl_ObjectsProcessData_tbl.ID

END
GO


Print '-- [ObjectsUpdate_ProcessStatus_Mark_LastRes__MaintainDB] ---------------'
If Exists (select Name
           from sysobjects
           where Name = "ObjectsUpdate_ProcessStatus_Mark_LastRes__MaintainDB" and type = "P")
Drop Procedure ObjectsUpdate_ProcessStatus_Mark_LastRes__MaintainDB
GO

CREATE Procedure ObjectsUpdate_ProcessStatus_Mark_LastRes__MaintainDB

/* Makiert den LastRes Datensatz in dbo.JobControl_ObjectsProcessData_tbl  erstellt 23.02.2009 J.Fes */
As
BEGIN
  -- 05.03.2011 12:10 eingefuegt
  set nocount on

  if (object_id('tempdb..#zztmpMAX1_tbl') is not null)  Drop table #zztmpMAX1_tbl
  if (object_id('tempdb..#zztmpMAX2_tbl') is not null)  Drop table #zztmpMAX2_tbl

  -- Step: auffinden des .. der Last Result Datensätze in Tabelle: dbo.JobControl_ObjectsProcessData_tbl
  SELECT
    MAX(dbo.JobControl_ObjectsProcessData_tbl.ProcessTimeStart) AS ProcessTimeStart_MAX,
    dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID ,
    dbo.JobControl_ObjectsProcessData_tbl.ProcessProcStateCol
  INTO #zztmpMAX1_tbl
  FROM
    dbo.JobControl_ObjectsProcessData_tbl -- with (nolock)
  -- WHERE  -- wenn @Objects_tbl_ID='' --> entfält WHERE clausel
    -- dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID = @Objects_tbl_ID
  GROUP BY
     dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID
   , dbo.JobControl_ObjectsProcessData_tbl.ProcessProcStateCol

  -- Step: dbo.JobControl_ObjectsProcessData_tbl.ID's aller Last Result Datensätze ermitteln
  -- siehe hierzu auch http://www.eggheadcafe.com/conversation.aspx?messageid=30818900&threadid=30818889 (28.02.2009 09:40)
  -- CodeExample: vergleichbar mit first(..) Dataset
  SELECT *
   Into #zztmpMAX2_tbl
  from
    (Select dbo.JobControl_ObjectsProcessData_tbl.ID
           , dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID
           -- , rn = row_number() OVER (PARTITION BY dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID ORDER BY dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID
           , rn = row_number() OVER (PARTITION BY #zztmpMAX1_tbl.Objects_tbl_ID, #zztmpMAX1_tbl.ProcessProcStateCol ORDER BY #zztmpMAX1_tbl.Objects_tbl_ID)
     from
      dbo.JobControl_ObjectsProcessData_tbl ,
      #zztmpMAX1_tbl -- with (nolock)
     Where
          dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID       = #zztmpMAX1_tbl.Objects_tbl_ID
      and dbo.JobControl_ObjectsProcessData_tbl.ProcessProcStateCol  = #zztmpMAX1_tbl.ProcessProcStateCol
      and dbo.JobControl_ObjectsProcessData_tbl.ProcessTimeStart     = #zztmpMAX1_tbl.ProcessTimeStart_MAX
     ) As SelectSub
  where SelectSub.rn=1

  -- Step: alle Objekte die nicht Last Result sind auf 0 setzen
  Update dbo.JobControl_ObjectsProcessData_tbl set dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes = 0
   where
     (dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes <> 0 or dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes is NULL) and
      -- wenn @Objects_tbl_ID='' entfaellt die nachfolgende Zeile
      -- dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID = @Objects_tbl_ID and
      dbo.JobControl_ObjectsProcessData_tbl.ID not IN (select #zztmpMAX2_tbl.ID from #zztmpMAX2_tbl)

  -- Step: alle anderen Objekte Last Result ist auf 1 setzen
  Update dbo.JobControl_ObjectsProcessData_tbl set dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes = 1
   where
     (dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes <> 1 or dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes is NULL) and
      dbo.JobControl_ObjectsProcessData_tbl.ID IN (select #zztmpMAX2_tbl.ID from #zztmpMAX2_tbl)

END
GO


---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------


-- Print '----------------------------------------------------'
-- Print ' Test Procedures ObjectsUpdate_ProcessStatus_Mark_LastRes'
-- Print '----------------------------------------------------'
-- GO
--
-- -- Execute ObjectsUpdate_ProcessStatus_Mark_LastRes '' -- '63bdd795-be19-4a07-93fb-f9260a8d3787' -- ''
-- GROB DB
-- Execute ObjectsUpdate_ProcessStatus_Mark_LastRes '51EA3694-A4BF-4934-89A6-DA0F2F51C5AA'
--   EXECUTE zzzzzDebugTbl_Empty
--   EXECUTE  Objects_tbl_UpdateColumeValue_by_ID '30157A93-358E-4525-AA94-0382A24B6368', 'ProcStateColRf', 'XX', 'SetPStateTo_UgMaster_Off_DS'
-- goto ende
ende:

Print '-------------------------------------------------------------------------'
Print ' Procedures imported to #DB_N#'
Print '-------------------------------------------------------------------------'
GO

