IF EXISTS (
    SELECT 1 
    FROM sys.objects 
    WHERE object_id = OBJECT_ID('[aps].[sp_UpdateRecipeLastStatusHistory]')
)
    DROP PROCEDURE [APS].[sp_UpdateRecipeLastStatusHistory]
GO

CREATE PROCEDURE [APS].[sp_UpdateRecipeLastStatusHistory]
(
    @RecipeId UNIQUEIDENTIFIER
)
AS
BEGIN
    SET NOCOUNT ON;

    WITH LatestStatusHistory AS (
        SELECT 
            sh.CreationDateTime,
            sh.Recipe,
            sh.RecipeStatusHistoryID
        FROM 
            [APS].[T_RECIPE_STATUS_HISTORY] AS sh
        WHERE 
            sh.Recipe = @RecipeId
            AND NOT EXISTS (
                SELECT 1 
                FROM [APS].[T_RECIPE_STATUS_HISTORY] AS sh2
                WHERE 
                    sh.Recipe = sh2.Recipe
                    AND sh2.CreationDateTime > sh.CreationDateTime
            )
    )    
    UPDATE r
    SET 
        r.LastStatusHistory = lsh.RecipeStatusHistoryID
    FROM [APS].[T_RECIPE] AS r
    JOIN LatestStatusHistory AS lsh ON r.RecipeID = lsh.Recipe
END
GO
