SQL to get a list of running journeys that haven't sent any emails in the past 90 days

Question

I ran a simple SQL to see how many 'running' journeys I have in my system. There are hundreds.

This is the data I want: To get a unique list of journeys in SFMC that have a journey status of "running" but there have been zero sends in the past three months.

Below is the SQL I've written. Issues I'm facing: I either get multiple rows (And if I manually delete it, it doesn't seem accurate) or I get data without Event Date.

Happy to get some recommendations on how to approach the overall objective too, not just the sql.

SELECT
    j.VersionID,
    j.JourneyID,
    j.JourneyName,
    j.VersionNumber,
    j.CreatedDate,
    j.LastPublishedDate,
    j.ModifiedDate,
    j.JourneyStatus,
    s.EventDate
FROM _Journey j
LEFT JOIN _JourneyActivity ja ON ja.VersionID = j.VersionID
LEFT JOIN _Sent s ON s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
WHERE j.JourneyStatus = 'Running'
AND s.EventDate <= DATEADD(month, -3, GETDATE())
GROUP BY 
    j.VersionID,
    j.JourneyID,
    j.JourneyName,
    j.VersionNumber,
    j.CreatedDate,
    j.LastPublishedDate,
    j.ModifiedDate,
    j.JourneyStatus,
    s.EventDate

Answer 1

The code you have is probably returning Journeys with sends in the last 3 months, instead of the opposite.

The issue is the LEFT JOIN with Send: it's going to return either 0 or the number of Sends that you did, resulting in multiple records.

I'd move that part to a NOT IN filter:

SELECT
    j.VersionID,
    j.JourneyID,
    j.JourneyName,
    j.VersionNumber,
    j.CreatedDate,
    j.LastPublishedDate,
    j.ModifiedDate,
    j.JourneyStatus
FROM _Journey j
INNER JOIN _JourneyActivity ja ON ja.VersionID = j.VersionID
LEFT JOIN _Sent s ON s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
WHERE 
j.JourneyStatus = 'Running' AND 
ja.JourneyActivityObjectID NOT IN (
SELECT DISTINCT ja.JourneyActivityObjectID FROM _JourneyActivity ja INNER JOIN _Sent s ON ja.JourneyActivityObjectID = s.JourneyActivityObjectID WHERE 
s.EventDate <= DATEADD(month, -3, GETDATE())
GROUP BY 
    j.VersionID,
    j.JourneyID,
    j.JourneyName,
    j.VersionNumber,
    j.CreatedDate,
    j.LastPublishedDate,
    j.ModifiedDate,
    j.JourneyStatus

In DESelect we have this article explaining how Data Views work, in case that helps too.