| How to query and not include weekend or Bankholidays [message #660933] |
Thu, 02 March 2017 01:01  |
 |
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 #660936 is a reply to message #660933] |
Thu, 02 March 2017 02:17  |
 |
Barbara Boehmer
Messages: 9106
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
