：
CREATE TABLE Holidays ( HolidayDates ) AS
SELECT DATE '2018-12-25' FROM DUAL UNION ALL
SELECT DATE '2018-12-31' FROM DUAL;
CREATE TABLE table_name ( start_date, end_date ) AS
SELECT DATE '2018-12-21', DATE '2018-12-26' FROM DUAL UNION ALL
SELECT DATE '2018-12-28', DATE '2019-01-01' FROM DUAL;
：
SELECT t.*,
( TRUNC( end_date, 'IW' ) - TRUNC( start_date, 'IW' ) ) * 5 / 7
+ LEAST( TRUNC( end_date ) - TRUNC( end_date, 'IW' ) + 1, 5 )
- LEAST( TRUNC( start_date ) - TRUNC( start_date, 'IW' ), 5 )
- ( SELECT COUNT(1)
FROM holidays
WHERE HolidayDates BETWEEN t.start_date AND t.end_date
-- Exclude any weekend holidays so we don't double count.
AND TRUNC( HolidayDates ) - TRUNC( HolidayDates, 'IW' ) <= 5
)
AS Num_Week_Days
FROM table_name t;
：
START_DATE | END_DATE | NUM_WEEK_DAYS
:--------- | :-------- | ------------:
21-DEC-18 | 26-DEC-18 | 3
28-DEC-18 | 01-JAN-19 | 2
01-JAN-19 | 07-JAN-19 | 5
db <>在这里拨弄
602392714
清零编程群