Home » SQL & PL/SQL » SQL & PL/SQL » How to query and not include weekend or Bankholidays (SQL)
How to query and not include weekend or Bankholidays [message #660933] Thu, 02 March 2017 01:01 Go to next message
timppap
Messages: 1
Registered: March 2017
Junior Member
Hi ! I'd like to make a query from current day minus one business day (not include weekend or bank holidays, I've the following code in the Where clause

Where
(
TO_CHAR(table0.OPPRETTET_DATO,'yyyymmdd')>= to_char(current date -1 days,'yyyymmdd') or TO_CHAR(table0.avsluttet_DATO,'yyyymmdd') >= to_char(current date -1 days,'yyyymmdd'))


//Timo
Re: How to query and not include weekend or Bankholidays [message #660934 is a reply to message #660933] Thu, 02 March 2017 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

1/ Your WHERE clause is invalid
2/ You must specify what you current query should return
3/ Your query should likely contain a reference to a table that contains the holidays

Re: How to query and not include weekend or Bankholidays [message #660936 is a reply to message #660933] Thu, 02 March 2017 02:17 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The following example assumes that your date columns are of DATE data type as they should be.

-- If you have table0 values as below:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM table0 ORDER BY 1
  2  /

OPPRETTET_DATO  AVSLUTTET_DATO
--------------- ---------------
Tue 21-Feb-2017 Tue 21-Feb-2017
Wed 22-Feb-2017 Wed 22-Feb-2017
Thu 23-Feb-2017 Thu 23-Feb-2017
Fri 24-Feb-2017 Fri 24-Feb-2017
Sat 25-Feb-2017 Sat 25-Feb-2017
Sun 26-Feb-2017 Sun 26-Feb-2017
Mon 27-Feb-2017 Mon 27-Feb-2017
Tue 28-Feb-2017 Tue 28-Feb-2017
Wed 01-Mar-2017 Wed 01-Mar-2017
Thu 02-Mar-2017 Thu 02-Mar-2017

10 rows selected.

-- and you have a table of bank holidays as below
-- (in this example, I have made the previous three days holidays for demonstration purposes):
SCOTT@orcl_12.1.0.2.0> SELECT * FROM bank_holidays ORDER BY 1
  2  /

BANK_HOLIDAY
---------------
Mon 27-Feb-2017
Tue 28-Feb-2017
Wed 01-Mar-2017

3 rows selected.

-- then you can select values for today and the previous business day, not including weekends or holidays as below
-- (in this example, this is Thursday and the preceding Monday, Tuesday, and Wednesday, are holidays,
-- so the previous business day was Friday, so the query returns values for today and the preceeding Friday):
SCOTT@orcl_12.1.0.2.0> SELECT *
  2  FROM   table0,
  3  	    (SELECT MAX (test_date) AS previous_business_day
  4  	     FROM   (SELECT TRUNC (SYSDATE) - ROWNUM AS test_date
  5  		     FROM   DUAL
  6  		     CONNECT BY LEVEL <= 10)
  7  	     WHERE  TO_CHAR (test_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') NOT IN ('SAT', 'SUN')
  8  	     AND    test_date NOT IN (SELECT bank_holiday FROM bank_holidays))
  9  WHERE  table0.OPPRETTET_DATO >= previous_business_day
 10  AND    TO_CHAR (table0.OPPRETTET_DATO, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') NOT IN ('SAT', 'SUN')
 11  AND    TRUNC (table0.OPPRETTET_DATO) NOT IN (SELECT bank_holiday FROM bank_holidays)
 12  AND    TO_CHAR (table0.avsluttet_DATO, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') NOT IN ('SAT', 'SUN')
 13  AND    TRUNC (table0.avsluttet_DATO) NOT IN (SELECT bank_holiday FROM bank_holidays)
 14  ORDER  BY 1
 15  /

OPPRETTET_DATO  AVSLUTTET_DATO  PREVIOUS_BUSINE
--------------- --------------- ---------------
Fri 24-Feb-2017 Fri 24-Feb-2017 Fri 24-Feb-2017
Thu 02-Mar-2017 Thu 02-Mar-2017 Fri 24-Feb-2017

2 rows selected.
Previous Topic: Carriage return in column data for spool
Next Topic: {} in SQL
Goto Forum:
  


Current Time: Fri Mar 29 10:32:15 CDT 2024