Skip to content

Instantly share code, notes, and snippets.

@wvpv
Created July 24, 2023 13:45
Show Gist options
  • Save wvpv/a802be879f3da6d220e4fba156df2d1a to your computer and use it in GitHub Desktop.
Save wvpv/a802be879f3da6d220e4fba156df2d1a to your computer and use it in GitHub Desktop.
Journey Jobs
select
j.jobid
, j.emailName
, jn.journeyName
, max(jn.versionNumber) versionNumber
from _Job j
inner join (
select
convert(varchar(36), ja0.VersionID) VersionID /* UniqueIdentifier datatype doesnt play nicely with JO-INs */
, convert(varchar(36), ja0.ActivityID) ActivityID
, ja0.ActivityName
, ja0.ActivityExternalKey
, ja0.JourneyActivityObjectID
, ja0.ActivityType
from _JourneyActivity ja0
) ja on ja.JourneyActivityObjectID = j.TriggererSendDefinitionObjectID
inner join (
select
convert(varchar(36), j0.VersionID) VersionID
, convert(varchar(36), j0.JourneyID) JourneyID
, j0.JourneyName
, j0.VersionNumber
, j0.CreatedDate
, j0.LastPublishedDate
, j0.ModifiedDate
, j0.JourneyStatus
from _Journey j0
) j on j.versionid = ja.versionid
group by
j.jobid
, j.emailName
, jn.journeyName
/* name: JourneyJobs */
/* target: JourneyJobs */
/* action: update */
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment