CODEWITHSUNDEEP
sqloracle-database
user8340125
user8340125

Reputation:

Last business day of quarter

How do I find the last business day of a quarter? No Sat/Sun. Holiday is fine.

Below gives me the last day of last quarter but I need to find the last business day. 

SELECT TRUNC(SYSDATE, 'Q') - 1 LAST_DAY_OF_LAST_QTR
FROM DUAL

Upvotes: 0

Views: 1304

Answers (4)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

There's a trivial solution, if your quarterly last day is sunday, you'll go back for 2 days, or if saturday, you'll go back for 1 day. Then, by the aid of decode function, you may use :

SQL> col LAST_DAY_OF_LAST_QTR format a22
SQL> select decode(to_char(trunc(sysdate,'Q')-1,'D'),
                         7,trunc(sysdate,'Q')-2, -- for saturday
                         1,trunc(sysdate,'Q')-3, -- for sunday
                           trunc(sysdate,'Q')-1  -- for other days
                  ) LAST_DAY_OF_LAST_QTR from dual;

assuming you get value 1 for Sunday, 7 for saturday

with respect to your local setting of nls_date_language in the following query : 

select * from
(
 select to_char(sysdate-level,'D') day_num, to_char(sysdate-level,'Day') day
   from dual
  connect by level <= 7
) 
 order by day_num

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59602

This one is independent from current user NLS_TERRITORY and NLS_DATE_LANGUAGE settings:

select decode(to_char(trunc(sysdate,'Q')-1,'Dy', 'NLS_DATE_LANGUAGE = american'),
                     'Sat',trunc(sysdate,'Q')-2, -- for saturday
                     'Sun',trunc(sysdate,'Q')-3, -- for sunday
                       trunc(sysdate,'Q')-1  -- for other days
              ) LAST_DAY_OF_LAST_QTR from dual;

Upvotes: 1

eifla001
eifla001

Reputation: 1157

How about this?, 6 is Friday in the query.

SELECT NEXT_DAY(TRUNC(SYSDATE, 'Q') - 7, 6) LAST_FRIDAY_OF_LAST_QTR
  FROM DUAL;

For the last day, see 3 sample queries below.

17 is Sunday(1) and Saturday(7). If Sunday/Saturday is the last day then it will return the last friday, else return the last day.

SELECT DECODE(INSTR('17', TO_CHAR(TRUNC(TO_DATE('01-JAN-2019', 'DD-MON-YYYY'), 'Q')-1, 'D'))
               , 0, TRUNC(TO_DATE('01-JAN-2019', 'DD-MON-YYYY'), 'Q')-1, 
                    NEXT_DAY(TRUNC(TO_DATE('01-JAN-2019', 'DD-MON-YYYY'), 'Q') - 7, 6)
              )  LAST_BUSINESS_DAY_OF_LAST_QTR
   FROM DUAL;

SELECT DECODE(INSTR('17', TO_CHAR(TRUNC(TO_DATE('01-AUG-2019', 'DD-MON-YYYY'), 'Q')-1, 'D'))
               , 0, TRUNC(TO_DATE('01-AUG-2019', 'DD-MON-YYYY'), 'Q')-1, 
                    NEXT_DAY(TRUNC(TO_DATE('01-AUG-2019', 'DD-MON-YYYY'), 'Q') - 7, 6)
              )  LAST_BUSINESS_DAY_OF_LAST_QTR
   FROM DUAL;

SELECT DECODE(INSTR('17', TO_CHAR(TRUNC(TO_DATE('01-OCT-2019', 'DD-MON-YYYY'), 'Q')-1, 'D'))
               , 0, TRUNC(TO_DATE('01-OCT-2019', 'DD-MON-YYYY'), 'Q')-1, 
                    NEXT_DAY(TRUNC(TO_DATE('01-OCT-2019', 'DD-MON-YYYY'), 'Q') - 7, 6)
              )  LAST_BUSINESS_DAY_OF_LAST_QTR
   FROM DUAL;

Upvotes: 0

Patrick H
Patrick H

Reputation: 653

I'm sure there is a more elegant solution, but this is what I came up with:

Here is what I am assuming about your system:

  • Business days are Monday-Friday
  • Your first day of the week starts on Sunday in your Oracle database. (This can change based on country/territory settings)
with data(r) as
(select 0 r from dual
 union all
 select r+1 from data where r < 6
 )
select max(countingdays) lastbusinessdayofqtr
from (
    select trunc(sysdate, 'Q') - 1 - r countingdays,
           case when to_char(trunc(sysdate, 'Q') - 1 - r,'D') between 2 and 6 then 'Y' else 'N' end BusinessDay
    from data)
    where businessday = 'Y';

The with statement generates rows of numbers from 0 to 6. I use those numbers to subtract from the last day of the quarter. Any days between 2 and 6 are Monday - Friday. Finally I take the max date where businessday is 'Y'.

Upvotes: 1

Related Questions