Skip to Main Content
  • Questions
  • Show previous YTD if query is ran in January else Current YTD

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Marc.

Asked: December 20, 2018 - 4:30 pm UTC

Last updated: December 20, 2018 - 5:04 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

I'm looking for the logic to filter data Previous YTD if the query is ran in January else filter Current YTD. Any help would be greatly appreciated.

Thank you

Marc

and Chris said...

YTD => Year to Date, right?

So if you're in January you want the whole of the previous year?

If so, you can find the start date by:

- Subtracting one month from the current date
- Returning the start of the year for the corresponding date with the 'y' mask of trunc

And the end date as:

- If the current month = January, return the start of the year (assuming non-exclusive boundary; i.e. all dates strictly less than the end)
- Otherwise current date

alter system set fixed_date = '2018-06-02';

select trunc ( add_months ( sysdate, -1 ), 'y' ) st,
       case
         when extract ( month from sysdate ) = 1 then
           trunc ( sysdate, 'y' )
         else
           sysdate
       end en
from   dual;

ST                     EN                     
01-JAN-2018 00:00:00   02-JUN-2018 00:00:00  

alter system set fixed_date = '2018-02-01';

select trunc ( add_months ( sysdate, -1 ), 'y' ) st,
       case
         when extract ( month from sysdate ) = 1 then
           trunc ( sysdate, 'y' )
         else
           sysdate
       end en
from   dual;

ST                     EN                     
01-JAN-2018 00:00:00   01-FEB-2018 00:00:00 

alter system set fixed_date = '2018-12-20';

select trunc ( add_months ( sysdate, -1 ), 'y' ) st,
       case
         when extract ( month from sysdate ) = 1 then
           trunc ( sysdate, 'y' )
         else
           sysdate
       end en
from   dual;

ST                     EN                     
01-JAN-2018 00:00:00   20-DEC-2018 00:00:00  

alter system set fixed_date = '2019-01-10';

select trunc ( add_months ( sysdate, -1 ), 'y' ) st,
       case
         when extract ( month from sysdate ) = 1 then
           trunc ( sysdate, 'y' )
         else
           sysdate
       end en
from   dual;

ST                     EN                     
01-JAN-2018 00:00:00   01-JAN-2019 00:00:00   

alter system set fixed_date = none;


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.