AnsweredAssumed Answered

Help with Prior Fiscal Month query

Question asked by Keri Taylor on Jan 23, 2018
Latest reply on Jan 23, 2018 by Keri Taylor

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

Outcomes