These are my available attributes:

11-OCT-18     10    2018
12-OCT-18     10    2018
13-OCT-18     10    2018
14-OCT-18     10    2018
15-OCT-18     10    2018
16-OCT-18     10    2018
17-OCT-18     10    2018

I need a logic that will pull last month data based on sysdate.

where DT_CURRENT_DT >=  trunc (sysdate, 'MM') - interval '1' month
and DT_CURRENT_DT <   trunc (sysdate, 'MM')

However when sysdate = November 2018 then data must be pulled from October 15th till end of October. For other months it must behave as code above.

UPDATE (NOV 11) - CASE code version:

                       WHEN extract ( month from trunc (sysdate, 'MM')) = 11 and extract ( year from trunc (sysdate, 'MM')) = 2018
                       THEN to_date('15-OCT-2018')
                       ELSE  trunc(sysdate, 'MM') - interval '1' month
AND DT_CURRENT_DT <   trunc (sysdate, 'MM') 

Try to change the where clause to the following:

where DT_CURRENT_DT >= trunc (sysdate, 'MM') - interval '1' month
  and DT_CURRENT_DT < trunc (sysdate, 'MM')
  and (
       trunc(sysdate, 'MM') != to_date('01-NOV-2018')
       or extract(day from DT_CURRENT_DT) >= 15
  • great thanks! I had to change month(current_date) to EXTRACT year. That way in November 2017 I will get entire October. The rule to extract last month date starting 15th day was to happen only when sysdate is November 2018. For every other case it should pull entire last month: where DT_CURRENT_DT >= trunc (to_date('06-NOV-17'), 'MM') - interval '1' month and DT_CURRENT_DT < trunc (to_date('06-NOV-17'), 'MM') and ( extract (year from (to_date('06-NOV-17'))) != 2018 or extract (day from (DT_CURRENT_DT)) >= 15 )
    – marcin2x4
    Nov 11 '18 at 11:35
  • I have changed the answer. Is the query now doing what you expect?
    – taffer
    Nov 11 '18 at 18:28
  • I noticed my first where logic didn't take entire logic into account. Your code is working as expected!!! Since case statement is acting exactly the same wanted to ask what is the difference. Is it performance wise to use where I understand?
    – marcin2x4
    Nov 13 '18 at 9:58
  • 1
    I don't know if there's a difference in performance, but there shouldn't be. Choose what you think is more readable.
    – taffer
    Nov 14 '18 at 20:31

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.