How to get only the records of last month by SQL

Question

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'?

Answer 1

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.