end of quarter (ignore dayoff)

[Former Member]

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 "between sysdate MINUS two years and sysdate PLUS two years". 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?

[ThomasZloch · Accepted solution]

This seems not the right forum for your post, if you briefly explain the context then some moderator could be moving it to the correct forum, or have a look out our forum overview

http://forums.sdn.sap.com/index.jspa?categoryID=1

and suggest the correct home yourself.

Thomas

[Former Member · Accepted solution]

Thomas, I can't find appropriate forum here, honestly. Maybe it should be "scripting", but I'm not sured. The problem is solved, so my question could be even removed as well, but maybe it can be useful for someone else.

[Former Member]

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

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(sysdate, (level - 8)*3), 'Q') start_of_quarter,
                          ADD_MONTHS(TRUNC(ADD_MONTHS(sysdate, (level - 8)*3), 'Q'), +3)-1 as end_of_quarter 
                   from dual
                   connect by level <= 16) 
select e1.start_of_quarter, nvl(max(c1.arcdate), e1.end_of_quarter) as actual_end_of_quarter 
from   calendar1 c1, 
       end1 e1
where  c1.arcdate(+) between e1.start_of_quarter
                         and e1.end_of_quarter
group by e1.start_of_quarter, e1.end_of_quarter