end of quarter (ignore dayoff)

building semantic layer
1

Hi all,

this is how do I calculate end of quarter ignoring days off.

with 
 
calendar1 as
(select * from 
(select to_date('29.03.2012','DD.MM.YYYY') as arcdate, 0 as dayoff from dual union all
 select to_date('30.03.2012','DD.MM.YYYY') as arcdate, 0 as dayoff from dual union all
 select to_date('31.03.2012','DD.MM.YYYY') as arcdate, 1 as dayoff from dual)
 where dayoff = 0), 
 
end1 as
(select TRUNC(ADD_MONTHS(to_date('25.03.2012','DD.MM.YYYY'), +1), 'Q')-1 as end_of_quarter from dual) 
 
select max(calendar1.arcdate) as end_of_quarter 
from calendar1, end1 
where calendar1.arcdate <= end1.end_of_quarter

I need to create list of end_of_quarter for period from 2010 till 2012 year. Please note, dayoff is not only Saturday and Sunday, but it’s also state holiday, etc. In real life table calendar1 is updated by central bank.

Could you please suggest me some decision?

ecivgamer (BOB member since 2011-05-27)

2

Here is decision (table calendar1 need to be updated with days off including state holidays) :

ecivgamer (BOB member since 2011-05-27)

3

Can somone (Michael, Jansi) :slight_smile: tell me why this got moved to the off-topic. :? Seems like the person has a ligitmate issue he’s trying to resolve (albeit why on earth they are trying to do that at universe/report level instead of at the ETL level is another story). Or perhaps telling him to phrase/title his post better would be a good idea too?

katullus :us: (BOB member since 2009-08-21)

4

I don’t believe it was moved. This is where the topic was initiated. I’ll move it to the Semantic Layer forum.

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

5

Thanks Michael.

ecivgamer, it appears you may have already resolved your issue, however you might want to give it a better description of the issue next time. :slight_smile:

katullus :us: (BOB member since 2009-08-21)