1

I have a report automation that run every first day in the month with the System Data View;

i want always get the last month, but i have problem that the syntax that the updates the two months ago too and as my sintax by the day in pickuptime

SELECT 
year(getdate()) AS [Year],
'BU1' AS [Business Unit],
COUNT(*) AS JobID,
DATENAME(month, PickupTime) AS [Month]
FROM _Job
WHERE AccountID = '100000001' AND PickupTime BETWEEN GETDATE()-31 AND GETDATE()
GROUP BY DATENAME(month, PickupTime)

How to get this with 'pickuptime'?

2

You can compare Month and Year DateParts for the current time minus one month...

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)

Bear in mind this would just give you the number of mass send Jobs sent last month. Tiggered Sends (including Journey Builder sends) work a little differently, with the PickupTime recorded as when the trigger was first published. Also make sure your Automation runs after midnight CST (UTC-6) on the first of the month.

  • So thanks, @Macca! :) – Joao Marano Aug 26 at 13:03

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.