0

This is my data:

enter image description here

I need a Fiscal Year to Date logic that will gather all previous months starting October. However, for October 2018 I want to start form 15th, for remaining years it must start from October 1st.

Expected result would be all dates from October till last day of previous month. For testing purpose I replaced sysdate, 'MM' with to_date('31-DEC-18'), 'MM'

Below is working but wondering if I can improve it...

PER_PERIOD_START_DATE between 

(CASE
WHEN (a.PER_MONTH_NUM = 10 and extract( year from a.PER_PERIOD_START_DATE) = 2018)
THEN Add_Months(Trunc(Add_Months(to_date('31-DEC-19'),6),'YYYY'),-3) +14
ELSE Add_Months(Trunc(Add_Months(to_date('31-DEC-19'),6),'YYYY'),-3) 
END
)
and trunc(to_date('31-DEC-19'), 'MM')-1

Final code:

where PER_PERIOD_START_DATE between 

(CASE
WHEN (PER_MONTH_NUM = 10 and extract( year from PER_PERIOD_START_DATE) = 2018)
THEN Add_Months(Trunc(Add_Months(sysdate,6),'YYYY'),-3) + 14
ELSE Add_Months(Trunc(Add_Months(sysdate,6),'YYYY'),-3) 
END
)
and trunc(sysdate, 'MM')-1
  • Where do PER_PERIOD_START_DATE and PER_PERIOD_TYPE come from? Your table has only one column. – taffer Nov 14 '18 at 21:08
  • 1
    Better use date literal DATE '2018-12-31' or specify the format like TO_DATE('31-DEC-18', 'DD-MON-RR', 'nls_date_language = american') – Wernfried Domscheit Nov 15 '18 at 13:28
  • What is the purpose of trunc (to_date('31-DEC-18'), 'MM') - interval '1' month? Just write DATE '2018-11-01' – Wernfried Domscheit Nov 15 '18 at 13:30
0

Below code fully meets the requirement:

PER_PERIOD_START_DATE between 

(CASE
WHEN (PER_MONTH_NUM = 10 and extract( year from PER_PERIOD_START_DATE) = 2018)
THEN Add_Months(Trunc(Add_Months(sysdate,3),'YYYY'),-3) + 14
ELSE Add_Months(Trunc(Add_Months(sysdate,3),'YYYY'),-3) 
END
)
and trunc(sysdate, 'MM')-1

Alternative:

PER_PERIOD_START_DATE between 

(CASE
WHEN (PER_MONTH_NUM = 10 and extract( year from PER_PERIOD_START_DATE) = 2018)
THEN '15-OCT-'||(EXTRACT (YEAR FROM ADD_MONTHS(sysdate, -9)))
ELSE '01-OCT-'||(EXTRACT (YEAR FROM ADD_MONTHS(sysdate, -9)))
END
)
and trunc(sysdate, 'MM')-1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.