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

How do I get the SQL Agent job that runs a subscription

SELECT
j.job_id JOB_ID
, TRY_CONVERT(uniqueidentifier, j.[name]) JOB_NAME
, c.[name] REPORT_NAME
, s.Description
FROM ReportServer.dbo.ReportSchedule rs
JOIN msdb.dbo.sysjobs j ON rs.ScheduleID = TRY_CONVERT(uniqueidentifier, j.[name])
JOIN ReportServer.dbo.Subscriptions s ON s.[SubscriptionID] = rs.[SubscriptionID]
JOIN ReportServer.dbo.Catalog c ON c.itemid = s.[Report_OID]
ORDER BY 3


The JOB_NAME is the SQL Agent job name