Sp_activate_dependent_job_status - from https://saldeloera.wordpress.com/2012/01/31/metadata-driven-etl-dependent-job-execution/

Sp_activate_dependent_job_status.sql

/* =======================================================================
Author:        Sal De Loera
Create date:   1/31/2012
Description:   Checks for parent jobs that have completed since the last run of the child.
Input:         N/A
Output:        Set the ETL_OBJ.STATUS_ID to ‘Ready’ status
========================================================================= */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Sp_activate_dependent_job_status]
AS
DECLARE @ProcessStartTime AS INT;
DECLARE @DependentJobCount AS INT;
DECLARE @ReadyJobCount AS INT;
DECLARE @ETL_OBJ_ID AS INT;
–get distinct ETL_OBJ list where status = ‘complete’
DECLARE MY_CURSOR CURSOR FOR
SELECT DISTINCT ted.ETL_OBJ_ID
FROM   CONFIG.T_ETL_DEPENDENCY ted
INNER JOIN CONFIG.T_ETL_OBJ teo WITH (NOLOCK)
ON ted.ETL_OBJ_ID = teo.ETL_OBJ_ID
INNER JOIN CONFIG.T_ETL_STATUS tes WITH (NOLOCK)
ON tes.ETL_STATUS_ID = teo.ETL_STATUS_ID
WHERE  tes.ETL_STATUS_DESC IN ( ‘Complete’ )
AND teo.IS_ENABLED_IND = ‘True’
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @ETL_OBJ_ID
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
–get a count of parent jobs that have a start date > the last complete date of the child job, then 
SET @ReadyJobCount = (SELECT Count(*)
FROM   [dbo].T_ETL_DEPENDENCY ted
INNER JOIN (SELECT ETL_OBJ_ID,
Max(STOP_DTTM) AS STOP_DTTM
FROM   [dbo].T_ETL_LOG WITH (NOLOCK)
GROUP  BY ETL_OBJ_ID) parent
ON parent.ETL_OBJ_ID = ted.PARENT_ETL_OBJ_ID
LEFT JOIN [dbo].T_ETL_LOG child WITH (NOLOCK)
ON child.ETL_OBJ_ID = ted.ETL_OBJ_ID
WHERE  parent.STOP_DTTM > child.STOP_DTTM
AND ted.ETL_OBJ_ID = @ETL_OBJ_ID)
–get dependent job count
SET @DependentJobCount = (SELECT Count(*)
FROM   [dbo].T_ETL_DEPENDENCY ted WITH (NOLOCK)
INNER JOIN CONFIG.T_ETL_OBJ teo WITH (NOLOCK)
ON ted.PARENT_ETL_OBJ_ID = teo.ETL_OBJ_ID
WHERE  ted.ETL_OBJ_ID = @ETL_OBJ_ID
AND teo.IS_ENABLED_IND = ‘True’)
–if the counts match, update T_ETL_CONTROL to reflect ‘Ready’ status
IF @DependentJobCount = @ReadyJobCount
BEGIN
UPDATE [dbo].T_ETL_OBJ
SET    ETL_STATUS_ID = (SELECT ETL_STATUS_ID
FROM   [dbo].T_ETL_STATUS WITH (NOLOCK)
WHERE  ETL_STATUS_DESC = ‘Ready’)
WHERE  ETL_OBJ_ID = @ETL_OBJ_ID
END
–END LOOP
FETCH NEXT FROM MY_CURSOR INTO @ETL_OBJ_ID
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR