I have the below queries in my automation.
Query 1:
SELECT
s.SubscriberKey,
FORMAT(DATEADD(hh,6,s.EventDate),'MM/dd/yyyy hh:mm:ss tt') AS EventDate,
s.JobID,
s.batchID AS BatchID,
s.TriggererSendDefinitionObjectID,
j.EmailID,
FORMAT(DATEADD(hh,6,j.ModifiedDate),'MM/dd/yyyy hh:mm:ss tt') AS JobModifiedDate
FROM [_Sent] s
JOIN [_Job] j
ON s.JobID = j.JobID
WHERE s.EventDate > FORMAT(DATEADD(dd,-1,getdate()),'yyyy-MM-dd 00:00:00') AND s.EventDate < FORMAT(DATEADD(dd,-0,getdate()),'yyyy-MM-dd 00:00:00')
AND j.SuppressTracking = 0
AND j.Category <> 'Test Send Emails'
AND
(LOWER(j.EmailName) NOT LIKE '%proof%' AND LOWER(j.EmailName) NOT LIKE '%test%')
Result DE: DE1 Fetches records
Query 2: This query does not fetches record. However, when the where condition is removed, it gives me records. This is happening because in my BU the sends are not happening through journey. So, is the understanding correct that 'not like' parameter is not working where journeyname does not have a value.
SELECT
s.SubscriberKey,
s.EventDate,
s.JobID,
s.BatchID,
s.JobModifiedDate,
s.TriggererSendDefinitionObjectID,
s.EmailID,
su.EmailAddress AS EMAIL,
jy.JourneyID,
'SENT' AS EventType
FROM [DE1] s
JOIN ENT._Subscribers su
ON s.SubscriberKey = su.SubscriberKey
LEFT JOIN [_JourneyActivity] ja
ON s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
LEFT JOIN [_Journey] jy
ON ja.VersionID = jy.VersionID***
where (lower(jy.journeyname) NOT LIKE '%test%')
The set of queries works fine, when in the BUs that are using Journey Builder for email sends