2017年1月6日 星期五

[程式類]PLSQL查詢工作日; plsql how to get business days

若在PLSQL想要查詢所傳日期參數月的所有工作天,則使用以下方法達成

WITH ALLWorkDate as
(
    select allday workday from (
        select allday from (
            select to_date(to_char(TO_DATE(:tdate,'YYYY/MM/DD'),'yyyy-mm')||'-01','yyyy-mm-dd')+rownum-1 allday
            from
                (select to_date(to_char(TO_DATE(:tdate,'YYYY/MM/DD'),'yyyy-mm')||'-01','yyyy-mm-dd'),to_date(add_months(to_date(to_char(TO_DATE(:tdate,'YYYY/MM/DD'),'yyyy-mm')||'-01','yyyy-mm-dd'),'yyyy-mm-dd'),1) from dual)
            connect by rownum <= add_months(to_date(to_char(TO_DATE(:tdate,'YYYY/MM/DD'),'yyyy-mm')||'-01','yyyy-mm-dd'),1)-to_date(to_char(TO_DATE(:tdate,'YYYY/MM/DD'),'yyyy-mm')||'-01','yyyy-mm-dd')
        ) where 1=1
            and to_char(allday,'D') >1
            and to_char(allday,'D') <7
            and allday not in (select Holiday from EBHoliday where to_char(Holiday,'yyyy-mm') = to_char(TO_DATE(:tdate,'YYYY/MM/DD'),'yyyy-mm'))
        order by allday desc
    )aa
),LastWorkDate as
(
    select workday
    from ALLWorkDate
    where rownum = 1
),Last2WorkDates as
(
    select workday
    from ALLWorkDate
    where rownum <= 2
)

其中ALLWorkDate會將所有該月的日期撈出,再去掉在'假日檔'裡的日期;
我遇到的題目是最後一個工作日, 及最後兩個工作日..
所以利用反向排序後, 再用rownum指定是一筆, 還是兩筆...