Search This Blog

Saturday 15 December 2012

Oracle / SQL Server Query for MTD (Month to Date), YTD (Year to Date), Rolling 12 Months, & Prior Year Sales

Recently I came across an interview and I had been asked for write an SQL Query from the table for Month To Date and Year To Date for sales amount from sales table.

Here is the synopsis:
If using Oracle use the trunc and numtoyminterval functions; if using SQL Server use the datediff function.
 
Oracle Month to Date:



SELECT sum(SALES_AMOUNT)
FROM SALES
WHERE DATEOFSALES BETWEEN trunc(sysdate, 'MONTH') and sysdate

Oracle Year to Date:




SELECT sum(SALES_AMOUNT)
FROM SALES
WHERE DATEOFSALES BETWEEN trunc(sysdate, 'YEAR') and sysdate

 
Oracle Rolling 12 Months:



select sum(SALES_AMOUNT)
FROM SALES
WHERE DATEOFSALES between trunc(sysdate - numtoyminterval(12, 'MONTH'),
 'MONTH') and sysdate

 
Oracle Prior Year:



SELECT sum(SALES_AMOUNT)
FROM SALES
WHERE trunc(DATEOFSALES, 'YEAR') = trunc(sysdate - numtoyminterval
(1, 'YEAR'), 'YEAR')

SQL Server Month to Date:





SELECT sum(SALES_AMOUNT)
FROM SALES
WHERE datediff(month, DATEOFSALES, getdate()) = 0
AND DATEOFSALES <= getdate()

 
SQL Server Year to Date:




SELECT sum(SALES_AMOUNT)
FROM SALES
WHERE datediff(year, DATEOFSALES, getdate()) = 0
AND DATEOFSALES <= getdate()

 
SQL Server Rolling 12 Months:




SELECT sum(SALES_AMOUNT)
FROM SALES
WHERE datediff(month, DATEOFSALES, getdate()) <= 12
AND DATEOFSALES <= getdate()

 
SQL Server Prior Year:



SELECT sum(SALES_AMOUNT)
FROM SALES
WHERE datediff(year, DATEOFSALES, getdate()) = 1


Hope it is useful.
Cheers!!!
Bose



1 comment:

OBIEE 12c to OAS Migration

 Hi All, I have migrated successfully few projects from BI 12c to OAS 6.4. If  anyone wants help then please reach Me! Cheers! Bose

Recent Posts