0

Is there a way to count with SQL the sents excluding the jobid from test sent?

My sintax below:

SELECT 
DATEPART(yyyy,GETDATE()) AS [Year],
'BU1' AS [Business Unit],
COUNT(*) AS JobID,
DATEPART(m,PickupTime) AS [Month]
FROM
_Job
WHERE
AccountID = '100000001'
AND DATEPART(m, PickupTime) = DATEPART(m, DATEADD(m, -1, getdate()))
AND DATEPART(yyyy, PickupTime) = DATEPART(yyyy, DATEADD(m, -1, getdate()))
GROUP BY
DATEPART(m,PickupTime)
  • Have you tried filtering by JobType or TestEmailAddr? – zuzannamj Aug 27 '19 at 20:43
  • No :/ How could i do this with JobType? – Joao Marano Aug 27 '19 at 21:42
2

You should be able to achieve this by adding the following condition to your query:

WHERE TestEmailAddr = ''

TestEmailAddr column will only be populated for emails which were sent as test sends, so if you filter them out, you will get all the remaining sends.

You also need to check your query syntax, as you are trying to query a column which is not part of the _Job data view: BU1

A list of all _Job columns can be found here: https://help.salesforce.com/articleView?id=mc_as_data_view_job.htm&type=5&sfdcIFrameOrigin=nullrigin=null

| improve this answer | |
1

These test sends also have a Category of Test Send Emails so you could have your query have the following in the where clause:

where category != 'Test Send Emails'

| improve this answer | |

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.