Keeping Business Days in the Query

[satyakumar]

Hi Guys,

Below is my Query in Oracle,

can any one tell me how do i can keep the Business Days(From Mon- Friday) Only.

The story for this Report...

The Agent does a "FAN DATE" That means Delivered the Goods.
Once he does the FAN DATE he has to Process it to the company with in 24 Hours (Exclude Saturday and Sunday).

That means in this Report i have to show only the Waybills thats not processed with in 24 hours only the Business days.
I just want to Exclude Saturday and Sunday days.


SELECT E.AGENT_CODE,
A.NAME,
E.WAYBILL,
E.OCCURRED AS "FAN DATE",
E.PROCESSED AS "POSTED DATE",
S.DELIVERY_END AS "SPREAD END DATE"


FROM
EVENTS E
LEFT JOIN SHIPMENT_REQUESTS S ON E.WAYBILL = S.WAYBILL AND E.AGENT_CODE = S.DESTINATION_AGENT_CODE
LEFT JOIN SHIPMENT_REQUEST_STATUSES SRS ON S.SHIPMENT_REQUEST_ID = SRS.SHIPMENT_REQUEST_ID
LEFT JOIN SHIPMENT_STATUSES SS ON SRS.SHIPMENT_STATUS_ID= SS.SHIPMENT_STATUS_ID
LEFT JOIN AGENTS A ON E.AGENT_CODE = A.AGENT_CODE
WHERE
E.AGENT_CODE IN ('0522','0692','0514')
AND E.EVENT_CODE = 'FO'
AND TRUNC(E.PROCESSED) > TRUNC(E.OCCURRED) + 1
AND E.OCCURRED >= ({?startdate})
AND E.OCCURRED <= ({?enddate})
AND SS.SHIPMENT_STATUS_CODE NOT IN('H','I','P','B','X','RE','E','U','ARCH')
AND NOT EXISTS (SELECT 'DUMMY' FROM SHIPMENT_REQUEST_STATUSES SRS1 WHERE S.SHIPMENT_REQUEST_ID = SRS1.SHIPMENT_REQUEST_ID
AND SRS1.SHIPMENT_REQUEST_STATUS_ID > SRS.SHIPMENT_REQUEST_STATUS_ID)

 

[SantaMufasa]

Satya,

Less than a dozen threads below yours is this: thread1177-1486969.

Let us know if Dagon's solution is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”