Query Month to Date, Year to Date, Rolling 12 Months, & Prior Year Sales

Synopsis:

If using Oracle use the trunc and numtoyminterval functions; if using SQL Server use the datediff function.

---

Oracle Month to Date:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE TRANSACTION_DATE BETWEEN trunc(sysdate, 'MONTH') and sysdate

Oracle Year to Date:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE TRANSACTION_DATE BETWEEN trunc(sysdate, 'YEAR') and sysdate

Oracle Rolling 12 Months:

select sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE TRANSACTION_DATE between trunc(sysdate - numtoyminterval(12, 'MONTH'), 'MONTH') and sysdate

Oracle Prior Year:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE trunc(TRANSACTION_DATE, 'YEAR') = trunc(sysdate - numtoyminterval(1, 'YEAR'), 'YEAR')

---

SQL Server Month to Date:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE datediff(month, TRANSACTION_DATE, getdate()) = 0
AND TRANSACTION_DATE <= getdate()

SQL Server Year to Date:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE datediff(year, TRANSACTION_DATE, getdate()) = 0
AND TRANSACTION_DATE <= getdate()

SQL Server Rolling 12 Months:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE datediff(month, TRANSACTION_DATE, getdate()) <= 12
AND TRANSACTION_DATE <= getdate()

SQL Server Prior Year:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE datediff(year, TRANSACTION_DATE, getdate()) = 1