Working Days Calculation using Oracle SQL

1.      To Calculate the number of Working Days between p_from_date AND p_to_date in p_organization_name inventory Org (i.e. except Saturdays and Sundays Only):

SELECT COUNT(1) no_of_work_days
FROM (SELECT *
FROM bom_calendar_dates bcd
WHERE bcd.calendar_code =
‘CAL ‘ ||
(SELECT organization_code
FROM org_organization_definitions
WHERE organization_name = p_organization_name)
AND EXISTS
(SELECT 1
FROM bom_calendar_exceptions bce
WHERE bce.exception_date = bcd.calendar_date
AND bce.calendar_code = bcd.calendar_code)
AND calendar_date between p_from_date AND p_to_date-1
UNION ALL
SELECT *
FROM bom_calendar_dates
WHERE calendar_code =
‘CAL ‘ ||
(SELECT organization_code
FROM org_organization_definitions
WHERE organization_name = p_organization_name)
AND calendar_date between p_from_date AND p_to_date-1
AND seq_num IS NOT NULL);

Note: Replace the values of p_from_date, p_to_date, and p_organization_name before executing the query.



2.      If the developer wants to subtract the number of WORKING DAYS(Except Saturday and Sunday ONLY) then use the following query:

SELECT calendar_date
FROM (SELECT calendar_date
FROM (SELECT calendar_date
FROM bom_calendar_dates bcd
WHERE bcd.calendar_code =
‘CAL ‘ ||
(SELECT organization_code
FROM org_organization_definitions
WHERE organization_name = p_organization_name)
AND EXISTS
(SELECT 1
FROM bom_calendar_exceptions bce
WHERE bce.exception_date = bcd.calendar_date
AND bce.calendar_code = bcd.calendar_code)
UNION ALL
SELECT calendar_date
FROM bom_calendar_dates
WHERE calendar_code =
‘CAL ‘ ||
(SELECT organization_code
FROM org_organization_definitions
WHERE organization_name = p_organization_name)
AND seq_num IS NOT NULL)
WHERE calendar_date < p_date
ORDER BY calendar_date DESC)
WHERE rownum <= p_no_of_days
ORDER BY calendar_date;

Note: Replace the values of p_no_of_days, p_date, and p_organization_name before executing the query.

bom_calendar_exceptions table contains all the holidays except Saturdays and Sundays. If the developer wants to include the holidays defined in the organization.

Leave a reply:

Your email address will not be published.

Sliding Sidebar