Dear All,
Yesterday, I posted general SQL regarding ignoring the WEEKENDS from the count of the query but all solutions failed. I am now pasting here actual SQL. This SQL returns delay_days which includes WEEKENDS also. I want it should ignore WEEKEND DAYS and only counts BUSINESS DAYS (WEEKDAYS). For example if any shipment is sent on Tuesday and if it reaches on Monday instead of 7 days it should return 5 days as delay_days while ignoring two weekend days.
Your help on this would highly be appreciated.
SELECT ROWNUM,batchname,statusdate,nrofshipments,CITYNAME,CITY_CODE,delay_days,NULL Courier_Delivery_Time
FROM
(
SELECT ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(batchname, '46', 'F'), '33', '#'), '3', NULL), '#', '3'), '4D', 'M'), '2D', '-'))batchid,a.statusdate,(TRUNC(SYSDATE)-TRUNC(a.statusdate)) delay_days,
a.nrofshipments,(c.CITYNAME) CITYNAME,c.citycode city_code
FROM batch a , cities c
WHERE TRUNC(a.statusdate) <=TRUNC(SYSDATE-3)
AND a.status=1200
AND SUBSTR(( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(a.batchname, '33', '#'), '3', NULL), '#', '3'), '4D', 'M'), '2D', '-')),1,4)=c.citycode
ORDER BY citycode,statusdate