0

I've written this query to return all jobs sent in the last month. For some reason, it's returning jobs as far back as March 2017 - a full 6 months. There should be about 120, but it's showing 9,000 rows. I know Data Views only keep 6 months of data, so it looks like the date part of this query isn't being read at all - can anyone help?

SELECT EmailName, EmailSubject, CreatedDate As sendDate
FROM _Job
WHERE [CreatedDate] >= DATEADD(MONTH, -1, CAST(GETDATE() AS date))
AND EmailSubject Not Like '%Test Send%'
AND EmailName Like '%-APP-%'
OR EmailName Like '%-HIS-%'
OR EmailName Like '%-PAD-%'
OR EmailName Like '%-EXT-%'
OR EmailName Like '%-UNP-%'
OR EmailName Like '%-CLE-%'
OR EmailName Like '%-UTT-%'
OR EmailName Like '%-PRO-%'
OR EmailName Like '%-PAR-%'
OR EmailName Like '%-ADV-%'
2
  • What is the earliest "created date" from your result? Can you verify that there are records beyond Month -1? – Samuel Dare Sep 6 '17 at 11:06
  • It looks like it's not filtering on date at all, and goes right from today to as far back as it can go, which is March 6th. – TCassa Sep 6 '17 at 11:32
2

The issue was due to the OR statements in your original. This was ignoring your original 'AND' WHERE clauses if the email name was similar to your listed strings.

To get around this, try the following enclosed in parenthesis.

SELECT EmailName, EmailSubject, CreatedDate As sendDate
FROM _Job
WHERE [CreatedDate] >= DATEADD(MONTH, -1, CAST(GETDATE() AS date))
AND EmailSubject Not Like '%Test Send%'
AND (EmailName Like '%-APP-%'
OR EmailName Like '%-HIS-%'
OR EmailName Like '%-PAD-%'
OR EmailName Like '%-EXT-%'
OR EmailName Like '%-UNP-%'
OR EmailName Like '%-CLE-%'
OR EmailName Like '%-UTT-%'
OR EmailName Like '%-PRO-%'
OR EmailName Like '%-PAR-%'
OR EmailName Like '%-ADV-%')

I also recommend using DateDiff as I have found it to be faster and more accurate in SFMC:
(just my opinion, others disagree)

WHERE DateDiff(month, sendDate, GetDate()) < 1

3
  • When I try to change CreatedDate to sendDate in the Where clause, it won't work - it can't find that column. I also tried using DateDiff and it still seems to be giving me too many results. – TCassa Sep 6 '17 at 14:46
  • 1
    @TCassa - changed answer as I believe I found the real issue – Gortonington Sep 6 '17 at 16:14
  • 1
    Thanks @Gortonington, that worked perfectly. I always forget parentheses! – TCassa Sep 7 '17 at 11:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.