Calculate Last Working Day

sliderrules

Hi,

I am using Oracle Database 11.2.1 and would like to return the last working day (DD/MM/YYYY).

The working days run from Mon-Fri

e.g. On Monday 18/06/2000 the value returned would be Fri 15/06/2000

jeneesh

sliderrules wrote:
I meant the working days of the week

e.g. Mon 13/05/2013 would return 10/05/2013
Tue 14/05/2013 would return 13/05/2013 etc

What about the query already provided?

SQL> with t as
  2      (
  3      select to_date('13/05/2013 ','dd/mm/yyyy') dt
  4      from dual
  5      union all
  6      select to_date('14/05/2013 ','dd/mm/yyyy') dt
  7      from dual
  8      )
  9      select dt,dt -
 10             case to_char(dt,'fmday','nls_language=english')
 11                         when 'sunday' then 2
 12                 when 'monday' then 3
 13                else 1
 14            end last_w_day
 15     from t;

DT        LAST_W_DA
--------- ---------
13-MAY-13 10-MAY-13
14-MAY-13 13-MAY-13

jeneesh

18/06/2000 is not monday
18/06/2001 is..

SQL> with t as
  2  (
  3  select to_date('18/06/2001','dd/mm/yyyy') dt
  4  from dual
  5  )
  6  select dt -
  7         case to_char(dt,'fmday')
  8                     when 'sunday' then 2
  9             when 'monday' then 3
 10             else 1
 11         end w_day
 12  from t
 13  /

W_DAY
---------
15-JUN-01

Karthick2003

sliderrules wrote:
Hi,

I am using Oracle Database 11.2.1 and would like to return the last working day (DD/MM/YYYY).

Last working day of week or month or year?

>

The working days run from Mon-Fri

e.g. On Monday 18/06/2000 the value returned would be Fri 15/06/2000

18/06/2000 is sunday

SQL> select to_char(to_date('18/06/2000', 'dd/mm/yyyy'), 'day') from dual;
 
TO_CHAR(T
---------
sunday

and 15/06/2000 is thursday

Not sure what you are talking about.

sliderrules

I meant the working days of the week

e.g. Mon 13/05/2013 would return 10/05/2013
Tue 14/05/2013 would return 13/05/2013 etc

jeneesh

sliderrules wrote:
I meant the working days of the week

e.g. Mon 13/05/2013 would return 10/05/2013
Tue 14/05/2013 would return 13/05/2013 etc

What about the query already provided?

SQL> with t as
  2      (
  3      select to_date('13/05/2013 ','dd/mm/yyyy') dt
  4      from dual
  5      union all
  6      select to_date('14/05/2013 ','dd/mm/yyyy') dt
  7      from dual
  8      )
  9      select dt,dt -
 10             case to_char(dt,'fmday','nls_language=english')
 11                         when 'sunday' then 2
 12                 when 'monday' then 3
 13                else 1
 14            end last_w_day
 15     from t;

DT        LAST_W_DA
--------- ---------
13-MAY-13 10-MAY-13
14-MAY-13 13-MAY-13

chris227

with data as (
select to_date('18/06/2000','DD/MM/YYYY') d, to_date('16/06/2013','DD/MM/YYYY')  result from dual
union all
select to_date('19/06/2013','DD/MM/YYYY') d, to_date('18/06/2013','DD/MM/YYYY')  from dual
union all
select to_date('20/06/2013','DD/MM/YYYY') d, to_date('19/06/2013','DD/MM/YYYY')  from dual
union all
select to_date('21/06/2013','DD/MM/YYYY') d, to_date('20/06/2013','DD/MM/YYYY')  from dual
union all
select to_date('22/06/2013','DD/MM/YYYY') d, to_date('21/06/2013','DD/MM/YYYY')  from dual
union all
select to_date('23/06/2013','DD/MM/YYYY') d, to_date('21/06/2013','DD/MM/YYYY')  from dual
union all
select to_date('24/06/2013','DD/MM/YYYY') d, to_date('21/06/2013','DD/MM/YYYY')  from dual
union all
select to_date('25/06/2013','DD/MM/YYYY') d, to_date('24/06/2013','DD/MM/YYYY')  from dual
union all
select to_date('26/06/2013','DD/MM/YYYY') d, to_date('25/06/2013','DD/MM/YYYY') from dual
)
 
select
 d
,d-greatest(
mod(to_char(d-1,'J'),7)+1-4
,1) last_wrk_day
,result
from data

D	LAST_WRK_DAY	RESULT
18.06.2000	16.06.2000	16.06.2010
19.06.2013	18.06.2013	18.06.2013
20.06.2013	19.06.2013	19.06.2013
21.06.2013	20.06.2013	20.06.2013
22.06.2013	21.06.2013	21.06.2013
23.06.2013	21.06.2013	21.06.2013
24.06.2013	21.06.2013	21.06.2013
25.06.2013	24.06.2013	24.06.2013
26.06.2013	25.06.2013	25.06.2013

Edited by: chris227 on 15.05.2013 05:47

kendenny

Use the next_day function. next_day(date,'DAY')

SQL> select to_char(next_day(sysdate,'FRI'),'dd/mm/yyyy') from dual;
 
TO_CHAR(NEXT_DAY(SYSDATE,'FRI'
------------------------------
17/05/2013

Of course if the date is already that day of the week then it will get the next one.

SQL> select to_char(next_day(sysdate+2,'FRI'),'dd/mm/yyyy') from dual;
 
TO_CHAR(NEXT_DAY(SYSDATE+2,'FR
------------------------------
24/05/2013

So you'll probably want to use date -1 in your calculation.
Edit. Sorry I misunderstood the question.

Edited by: kendenny on May 15, 2013 5:44 AM