Year-To-Date for Previous Years (Same Period Last Year)

0
votes

I've got transaction data spanning 3 years and I would like to report on performance for the different years on a Year-To-Date basis.

The data is like:

REP_NAME : SECTION : AMOUNT : DATE
Mark     : Sec1    : 2,000  : 01/10/2016
Jane     : Sec2    : 1,400  : 02/12/2017

And so on...

What I want to do is write a query that will group all the amounts per year but being controlled by the current date of the current year.

2016 : 2017 : 2018
2500 : 2300 : 3400

Showing, YTD for 2018 is 3400 (i.e. between 01/01/2018 - 29/03/2018), but same period the last two years (between 01/01/2017 - 29-03-2017) and (01/01/2016 and 29/03/2016) the collection was 2,300 and 2,500 respectively.

I hope this is clear. I am using Oracle Database.

Thanks.

2
sqloracledate-range

2 Answers

0
votes

Just filter those values where either the date is in an earlier month of the year or the date is in the same month of the year on an earlier-or-same day of the month:

SELECT EXTRACT( YEAR FROM "DATE" ) AS year,
       SUM( AMOUNT ) AS total_year_to_date_amount
FROM   your_table
WHERE  (  EXTRACT( MONTH FROM "DATE" ) < EXTRACT( MONTH FROM SYSDATE )
       OR (   EXTRACT( MONTH FROM "DATE" ) =  EXTRACT( MONTH FROM SYSDATE )
          AND EXTRACT( DAY   FROM "DATE" ) <= EXTRACT( DAY   FROM SYSDATE )
          )
       )
AND    "DATE" >= ADD_MONTHS( TRUNC( SYSDATE, 'YEAR' ), -24 )
GROUP BY EXTRACT( YEAR FROM "DATE" );

or you can explicitly set the ranges:

SELECT EXTRACT( YEAR FROM "DATE" ) AS year,
       SUM( AMOUNT ) AS total_year_to_date_amount
FROM   your_table
WHERE  "DATE" BETWEEN TRUNC( SYSDATE, 'YEAR' )
                  AND SYSDATE
OR     "DATE" BETWEEN ADD_MONTHS( TRUNC( SYSDATE, 'YEAR' ), -12 )
                  AND ADD_MONTHS( SYSDATE, -12 )
OR     "DATE" BETWEEN ADD_MONTHS( TRUNC( SYSDATE, 'YEAR' ), -24 )
                  AND ADD_MONTHS( SYSDATE, -24 )
GROUP BY EXTRACT( YEAR FROM "DATE" );

(Note: the first query will include all results from the current day-of-the-year - i.e. 00:00 - 23:39 - whereas the second query only include results from the current day-of-the-year that are between 00:00 and the current time. You could give similar behaviour to the second query - one method is by using TRUNC( "DATE" ).)

0
votes

You want to only consider certain days, i.e. January first till current day. So write a WHERE clause for this, looking at month and day, ignoring the year.

where to_char(date, 'mmdd') <= to_char(sysdate, 'mmdd')

Then you want sums per year. So group by year.

select
  extract(year from date) as year,
  sum(amount) as total
from mytable
where to_char(date, 'mmdd') <= to_char(sysdate, 'mmdd')
group by extract(year from date)
order by extract(year from date);