Clarity

Hello All, 

I am having trouble with a prior fiscal month query. I am using this to get my current fiscal month, 

SELECT MIN(b.start_date) start_date
           , MAX(b.end_date -1) end_date
FROM biz_com_periods b
WHERE b.entity_id = 5001021
AND   b.period_type = 'MONTHLY'
AND trunc(sysdate) BETWEEN b.start_date AND b.end_date-1

I am having trouble getting the prior fiscal month. I have tried this but i don't think its correct as this is a oracle function for month (1st of the month till end of the month) not for fiscal months we create.

SELECT MIN(b.start_date) start_date
           , MAX(b.end_date -1) end_date
FROM biz_com_periods b
WHERE b.entity_id = 5001021
AND   b.period_type = 'MONTHLY'
--AND trunc(sysdate) BETWEEN b.start_date AND b.end_date-1
AND trunc(sysdate,'month')-1 BETWEEN b.start_date AND b.end_date-1

Can someone please shed some light, dates are definitely hard for me. 

I need "x". 

Thanks! 

Keri

Probably not the most optimized.. But this seems to be working for me:

with temp as (
select id, start_date, end_date
, row_number() over (order by start_date desc) as row_num
from biz_com_periods
where period_type = 'MONTHLY'
and entity_id = 5001000
and start_date < sysdate
)
select start_date, end_date
from temp
where row_num = 2
;

You would have to replace the "entity_id".

Thank you, I will update and my report and see how this works, seems to be working for me now with a little adjustment. Thank you again.!