# 計算兩日期間的工作數量

SELECT ORDER_NUMBER,

( CASE WHEN ( OOH.BOOKED_DATE IS NOT NULL AND ooh.ATTRIBUTE16 LIKE '20________%' )
THEN  apps.SGDF_WORK_DAY( TO_DATE(
CASE WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '00:00:00'AND '09:30:00'
THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'09:30:00'
WHEN TO_CHAR(OOH.BOOKED_DATE, 'hh24:mi:ss') BETWEEN '09:30:00' AND '13:00:00'
THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'13:00:00'
WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '13:00:00'AND '16:30:00'
THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'16:30:00'
ELSE    TO_CHAR(OOH.BOOKED_DATE + 1,'YYYY-MM-DD')||' '||'09:30:00'
END  ,'YYYY-MM-DD HH24:MI:SS')
,to_date(ooh.ATTRIBUTE16 ,'YYYY-MM-DD HH24:MI:SS') )

+ ( CASE WHEN
TO_CHAR(TO_DATE(OOH.ATTRIBUTE16,'YYYY-MM-DD HH24:MI:SS'),'HH24')
>= TO_CHAR( TO_DATE(
CASE WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '00:00:00'AND '09:30:00'
THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'09:30:00'
WHEN TO_CHAR(OOH.BOOKED_DATE, 'hh24:mi:ss') BETWEEN '09:30:00' AND '13:00:00'
THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'13:00:00'
WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '13:00:00'AND '16:30:00'
THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'16:30:00'
ELSE    TO_CHAR(OOH.BOOKED_DATE + 1,'YYYY-MM-DD')||' '||'09:30:00'
END ,'YYYY-MM-DD HH24:MI:SS')
,'HH24')
THEN -1+ ROUND( ( TO_CHAR(TO_DATE(OOH.ATTRIBUTE16,'YYYY-MM-DD HH24:MI:SS'),'HH24')
-TO_CHAR( TO_DATE(
CASE WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '00:00:00'AND '09:30:00'
THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'09:30:00'
WHEN TO_CHAR(OOH.BOOKED_DATE, 'hh24:mi:ss') BETWEEN '09:30:00' AND '13:00:00'
THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'13:00:00'
WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '13:00:00'AND '16:30:00'
THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'16:30:00'
ELSE    TO_CHAR(OOH.BOOKED_DATE + 1,'YYYY-MM-DD')||' '||'09:30:00'
END ,'YYYY-MM-DD HH24:MI:SS' )
,'HH24'))/8.0 ,2)
ELSE
ROUND( ( TO_CHAR(TO_DATE(OOH.ATTRIBUTE16,'YYYY-MM-DD HH24:MI:SS'),'HH24')
-  TO_CHAR( TO_DATE(
CASE WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '00:00:00'AND '09:30:00'
THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD') ||' '||'09:30:00'
WHEN TO_CHAR(OOH.BOOKED_DATE, 'hh24:mi:ss') BETWEEN '09:30:00' AND '13:00:00'
THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD') ||' '||'13:00:00'
WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '13:00:00'AND '16:30:00'
THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD') ||' '||'16:30:00'
ELSE    TO_CHAR(OOH.BOOKED_DATE + 1,'YYYY-MM-DD')||' '||'09:30:00'
END ,'YYYY-MM-DD HH24:MI:SS' )
,'HH24')  )/8.0 ,2)
END
)
ELSE NULL
END ) AS CT3_系統至回單日,  --生管回單-系統發送時間

(CASE WHEN OOH.BOOKED_DATE IS NOT NULL THEN
to_date(
CASE WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '00:00:00'AND '09:30:00'
THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'09:30:00'
WHEN TO_CHAR(OOH.BOOKED_DATE, 'hh24:mi:ss') BETWEEN '09:30:00' AND '13:00:00'
THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'13:00:00'
WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '13:00:00'AND '16:30:00'
THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'16:30:00'
ELSE    TO_CHAR(OOH.BOOKED_DATE +1,'YYYY-MM-DD')||' '||'09:30:00'
END ,'YYYY-MM-DD HH24:MI:SS')
ELSE NULL
END )  SEND_DATE,

OOH.CREATION_DATE, OOH.BOOKED_DATE, ooh.ATTRIBUTE16 REPLY_DATE

WHERE OOH.ORG_ID IN (400,420)
AND NVL(OOH.CANCELLED_FLAG,'N')='N'
-- and OOH.ORDER_NUMBER NOT IN ('601000164','601000152','601000149')
-- AND OOH.ORDER_NUMBER LIKE  '60100%'  -- '60100230_'  --'601001550'

• 博文量
192
• 访问量
166305