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
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;