현재시간기준 (3)교대일자,교대구분 구하기

1. 교대일자,교대구분
   

   SELECT
       CASE
           WHEN TO_CHAR(SYSDATE,'HH24MISS') BETWEEN '070001' AND '235959' THEN
               TO_CHAR(SYSDATE,'YYYYMMDD')
           ELSE
               TO_CHAR(SYSDATE-NUMTODSINTERVAL('1','DAY'),'YYYYMMDD')
       END AS WDATE,
       CASE
           WHEN TO_CHAR(SYSDATE,'HH24MISS') BETWEEN '070001' AND '150000' THEN
               '1'
           WHEN TO_CHAR(SYSDATE,'HH24MISS') BETWEEN '150001' AND '230000' THEN
               '2'
           ELSE
               '3'
       END AS WSHIFT
   FROM DUAL​

2. 교대일자,교대구분의 업무시간구간
   

   SELECT
       (CASE WHEN TO_CHAR(SYSDATE,'HH24MISS') BETWEEN '070001' AND '235959' THEN TO_CHAR(SYSDATE,'YYYYMMDD') ELSE TO_CHAR(SYSDATE-NUMTODSINTERVAL('1','DAY'),'YYYYMMDD') END)||
       (CASE WHEN TO_CHAR(SYSDATE,'HH24MISS') BETWEEN '070001' AND '150000' THEN '070001' WHEN TO_CHAR(SYSDATE,'HH24MISS') BETWEEN '150001' AND '230000' THEN '150001' ELSE '230001' END)
       AS SDT,
       (CASE WHEN TO_CHAR(SYSDATE,'HH24MISS') BETWEEN '230001' AND '235959' THEN TO_CHAR(SYSDATE+NUMTODSINTERVAL('1','DAY'),'YYYYMMDD') ELSE TO_CHAR(SYSDATE,'YYYYMMDD') END)||
       (CASE WHEN TO_CHAR(SYSDATE,'HH24MISS') BETWEEN '070001' AND '150000' THEN '150000' WHEN TO_CHAR(SYSDATE,'HH24MISS') BETWEEN '150001' AND '230000' THEN '230000' ELSE '070000' END)
       AS EDT
   FROM DUAL​

    
3. 현재 교대일자,교대구분의 근무시간이 포함된 조회일시 구간을 입력 받았을때 조회시점까지만 조회
   

   WITH T( WDATE,WSHIFT,WRKDATETIME ) AS
   (
       SELECT '20161005','1','20161005070001' FROM DUAL UNION ALL
       SELECT '20161005','3','20161005230001' FROM DUAL UNION ALL
       SELECT '20161004','3','20161005065959' FROM DUAL UNION ALL
       SELECT '20161005','3','20161006000001' FROM DUAL UNION ALL
       SELECT '20161005','2','20161005150001' FROM DUAL UNION ALL
       SELECT '20161005','2','20161005225959' FROM DUAL UNION ALL
       SELECT '20161006','1','20161006080001' FROM DUAL
   )
   SELECT
       *
   FROM
       T
   WHERE
       WDATE= (CASE
               WHEN TO_CHAR(SYSDATE,'HH24MISS') BETWEEN '070001' AND '235959' THEN
                   TO_CHAR(SYSDATE,'YYYYMMDD')
               ELSE
                   TO_CHAR(SYSDATE-NUMTODSINTERVAL('1','DAY'),'YYYYMMDD')
               END)
   AND
       WSHIFT= (CASE
               WHEN TO_CHAR(SYSDATE,'HH24MISS') BETWEEN '070001' AND '150000' THEN
                   '1'
               WHEN TO_CHAR(SYSDATE,'HH24MISS') BETWEEN '150001' AND '230000' THEN
                   '2'
               ELSE
                   '3'
               END)
   AND
       WRKDATETIME <= (CASE
                       WHEN TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') BETWEEN :searchFromDate||:searchFromTime AND :searchToDate||:searchToTime THEN
                           TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
                       ELSE
                           '00000000000000'
                       END)​