Analytical Periods, MTD, YTD, Prior Year..

MTD: Month to date is a period starting from the beginning of the current month and ending at the current date.

SELECT SUM(SLS_AMT) FROM SALES WHERE DTOFSALES BETWEEN TRUNC(SYSDATE, 'MONTH') AND SYSDATE;

YTD: Year to date is a period starting January 1 (depending on the country) of the current year and ending today.

SELECT SUM(SLS_AMT) FROM SALES WHERE DTOFSALES BETWEEN TRUNC(SYSDATE, 'YEAR') AND SYSDATE;

Rolling 12 Months:

SELECT SUM(SLS_AMT) FROM SALES WHERE DTOFSALES BETWEEN TRUNC(SYSDATE - NUMTOYMINTERVAL(12, 'MONTH'), 'MONTH') AND SYSDATE;

Prior Year:

SELECT SUM(SLS_AMT) FROM SALES WHERE TRUNC(DATEOFSALES, 'YEAR') = TRUNC(SYSDATE - NUMTOYMINTERVAL(1, 'YEAR'), 'YEAR');
Advertisements