CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Vasantharaj Dava
Vasantharaj Dava

Reputation: 41

Last 5 Business days(exclude Sat & Sun) on Oracle Schdule

I have to find the last 5 business days using oracle schedule.

select sysdate as current_date,
       case when to_char(sysdate,'D') in (1,2,7)
            then next_day(sysdate-7,'Friday')
            else sysdate-1 end as prev_weekday1,
       case when to_char(sysdate,'D') in (1,2,7)
            then next_day(sysdate-7,'Friday')
            else sysdate-2 end as prev_weekday2,
       case when to_char(sysdate,'D') in (1,2,7)
            then next_day(sysdate-7,'Friday')
            else sysdate-3 end as prev_weekday3,
       case when to_char(sysdate,'D') in (1,2,7)
            then next_day(sysdate-7,'Friday')
            else sysdate-4 end as prev_weekday4,
       case when to_char(sysdate,'D') in (1,2,7)
            then next_day(sysdate-7,'Friday')
            else sysdate-5 end as prev_weekday5
from dual

Please help me is this way is correct or any other optimized way is there to.

Upvotes: 0

Views: 224

Answers (1)

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

select level from dual connect by level <=7 delivers results 1..7 for level.

Use this, to select the last seven days and remove Saturday and Sunday:

select 'prev_weekday_' || rownum as prev_weekday, d.dt
  from (select trunc(sysdate) - level dt,
               to_char(sysdate - level, 'D') w_day
          from dual
        connect by level <= 7) d
 where d.w_day <= 5
 order by d.dt desc;

/*
prev_weekday_1  14.07.2017
prev_weekday_2  13.07.2017
prev_weekday_3  12.07.2017
prev_weekday_4  11.07.2017
prev_weekday_5  10.07.2017
*/

If you need the results in columns then pivot:

select *
  from (select 'prev_weekday_' || rownum as prev_weekday, d.dt
          from (select trunc(sysdate) - level dt,
                       to_char(sysdate - level, 'D') w_day
                  from dual
                connect by level <= 7) d
         where d.w_day <= 5
         order by d.dt desc)
pivot(max(dt)
   for prev_weekday in('prev_weekday_1' as prev_weekday_1,
                       'prev_weekday_2' as prev_weekday_2,
                       'prev_weekday_3' as prev_weekday_3,
                       'prev_weekday_4' as prev_weekday_4,
                       'prev_weekday_5' as prev_weekday_5));
/*
prev_weekday_1  prev_weekday_2  prev_weekday_3  prev_weekday_4  prev_weekday_5
  14.07.2017     13.07.2017      12.07.2017      11.07.2017      10.07.2017
*/

Upvotes: 1

Related Questions