Conditional Data Driven Subscription

Here is an example of a stored procedure that can be used as the dataset query for a data-driven subscription. It conditionally executes the subscription by returning a row or no rows.

The logic is described below:

CREATE OR ALTER PROCEDURE [dbo].[SAMPLE_WEEKLY_DDQ]
AS
BEGIN
/*
Return a row to trigger the data driven subscription if the current
week is:

> the week of the last successful run of the Month End Processing job
AND
< the week of the next run of the Month End Processing job
*/
DECLARE
@TODAY DATE = GETDATE();

DECLARE
  @CURRENT_WEEK INT = DATEPART(week, @TODAY)
, @LAST_MONTH_END_WEEK INT = DATEPART(week, DATEADD(day, 1, EOMONTH(@TODAY, -1)))
, @NEXT_MONTH_END_WEEK INT = DATEPART(week, DATEADD(day, 1, EOMONTH(@TODAY)))
;

IF @CURRENT_WEEK > @LAST_MONTH_END_WEEK AND @CURRENT_WEEK < @NEXT_MONTH_END_WEEK
BEGIN
SELECT 1 -- return a row to trigger the data driven subscription
END
END
GO