|
想要找出下一个不是节假日的工作日
WITH HOLIDAY_TABLE AS
(SELECT to_date('25-dec-2014','dd-mon-yyyy') hday FROM dual
UNION ALL
SELECT to_date('26-dec-2014','dd-mon-yyyy') FROM dual
UNION ALL
SELECT to_date('01-jan-2015','dd-mon-yyyy') FROM dual
)
SELECT DT ,
TO_CHAR(DT,'DY') DAY_OF_WEEK ,
CASE
WHEN TO_CHAR(DT,'dy') IN ('sat','sun')
THEN 'WEEK_END'
WHEN EXISTS
(SELECT NULL FROM HOLIDAY_TABLE WHERE HDAY=DT
)
THEN 'HOLIDAY'
ELSE 'WEEK_DAY'
END AS DAY_IND
FROM
(SELECT to_date('19-dec-2014','dd-mon-yyyy') + level -1 dt
FROM DUAL
CONNECT BY LEVEL<=20
);
DT | DAY_OF_WEEK | day_ind | 2014-DEC-19 | FRI | WEEK_DAY | 2014-DEC-20 | SAT | WEEK_END | 2014-DEC-21 | SUN | WEEK_END | 2014-DEC-22 | MON | WEEK_DAY | 2014-DEC-23 | TUE | WEEK_DAY | 2014-DEC-24 | WED | WEEK_DAY | 2014-DEC-25 | THU | HOLIDAY | 2014-DEC-26 | FRI | HOLIDAY | 2014-DEC-27 | SAT | WEEK_END | 2014-DEC-28 | SUN | WEEK_END | 2014-DEC-29 | MON | WEEK_DAY | 2014-DEC-30 | TUE | WEEK_DAY | 2014-DEC-31 | WED | WEEK_DAY | 2015-JAN-01 | THU | HOLIDAY | 2015-JAN-02 | FRI | WEEK_DAY | 2015-JAN-03 | SAT | WEEK_END | 2015-JAN-04 | SUN | WEEK_END | 2015-JAN-05 | MON | WEEK_DAY | 2015-JAN-06 | TUE | WEEK_DAY | 2015-JAN-07 | WED | WEEK_DAY |
想得到下面的结果:
DT | Next_business_day | day_ind | 2014-DEC-19 | 2014-DEC-22 | WEEK_DAY | 2014-DEC-20 | 2014-DEC-22 | WEEK_END | 2014-DEC-21 | 2014-DEC-22 | WEEK_END | 2014-DEC-22 | 2014-DEC-23 | WEEK_DAY | 2014-DEC-23 | 2014-DEC-24 | WEEK_DAY | 2014-DEC-24 | 2014-DEC-29 | WEEK_DAY | 2014-DEC-25 | 2014-DEC-29 | HOLIDAY | 2014-DEC-26 | 2014-DEC-29 | HOLIDAY | 2014-DEC-27 | 2014-DEC-29 | WEEK_END | 2014-DEC-28 | 2014-DEC-29 | WEEK_END | 2014-DEC-29 | 2014-DEC-30 | WEEK_DAY | 2014-DEC-30 | 2014-DEC-31 | WEEK_DAY | 2014-DEC-31 | 2015-JAN-02 | WEEK_DAY | 2015-JAN-01 | 2015-JAN-02 | HOLIDAY | 2015-JAN-02 | 2015-JAN-05 | WEEK_DAY | 2015-JAN-03 | 2015-JAN-05 | WEEK_END | 2015-JAN-04 | 2015-JAN-05 | WEEK_END | 2015-JAN-05 | 2015-JAN-06 | WEEK_DAY | 2015-JAN-06 | 2015-JAN-07 | WEEK_DAY | 2015-JAN-07 | 2015-JAN-08 | WEEK_DAY |
|
|