Journey Jobs

JourneyJobs.sql

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 */