Search for a command to run...
Experienced Business Analyst with a demonstrated history of working in the airlines/aviation industry. Strong research professional with a engineer focused and a strong technical background. Skilled in SQL/PLSQL, Linux System Administration. A proud Oracle APEX Consultant.
Hello Jared, for holidays, I prefer to rely on a web service, I have updated the query.
Thank you! Lucas Hirschegger
Audit Information The Audit section captures key metadata about each component in your APEX application. Specifically, it records: Changed By: The username of the last developer who modified the component. Changed On: The timestamp of the last modi...
In this fifth post of The Standard Component Attributes Anatomy series, I’m focusing on the Configuration attribute—better known as Build Options. Since Build Options are managed in Shared Components, we’ll take a quick look at that area as well. Con...
Comments The Comment attribute might be one of the most universally available fields in Oracle APEX. It allows developers to quickly leave notes or remarks—for themselves or for teammates—that are never shown to end users during runtime. You'll find ...
In this fourth post of The Standard Component Attributes Anatomy Series, we’re diving deep into the Help standard attribute. Although often overlooked, the Help attribute plays an important role in improving the user experience by providing clear, co...
In this third post of The Standard Component Attributes Anatomy Series, I’m tackling the Security attribute. Since the Security section includes references to Authorization Schemes from Shared Components, we’ll briefly touch on that as well. And beca...
In a current project, I needed to calculate the date offset while excluding weekends. Initially, this was solved using a loop in PL/SQL, which I consider a less efficient approach.
To optimize it, I created a straightforward SQL query that handles this logic directly, ensuring only business days are considered.
I also enhanced the query to include the ability to move both backward and forward in time, all controlled by a single bind variable that defines the offset.
SELECT
d
FROM
(
SELECT
d,
ROWNUM rn
FROM
(
SELECT
sysdate + ( ( level ) * abs(:p_offset) / :p_offset ) d,
to_char(sysdate +(level), 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') AS target_day
FROM
dual
CONNECT BY
level <= abs(:p_offset) * 2
)
WHERE
target_day NOT IN ( 'SAT', 'SUN' )
)
WHERE
rn = abs(:p_offset);
In case you want to implement holidays as well, you can rely on a REST API for that:
WITH dy AS (
SELECT
sysdate + ( ( level ) * abs(:p_offset) / :p_offset ) d,
to_char(sysdate +(level), 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') AS target_day
FROM
dual
CONNECT BY
level <= abs(:p_offset) * 2
), dd AS (
SELECT
MIN(d) mind,
MAX(d) maxd
FROM
dy
), h AS (
SELECT
TO_DATE(holiday_date, 'yyyy-mm-dd') holiday_date,
holiday_type
FROM
JSON_TABLE ( apex_web_service.make_rest_request(p_url => 'https://date.nager.at/api/v3/PublicHolidays/2024/US', p_http_method => 'GET'
), '$[*]'
COLUMNS (
holiday_date VARCHAR2 ( 20 ) PATH '$.date',
holiday_type VARCHAR2 ( 50 ) PATH '$.types[0]'
)
)
WHERE
holiday_type = 'Public'
), hh AS (
SELECT
holiday_date
FROM
h
CROSS JOIN dd
WHERE
holiday_date BETWEEN mind AND maxd
)
SELECT
*
FROM
(
SELECT
d,
ROWNUM rn
FROM
(
SELECT
trunc(d) d
FROM
dy
WHERE
target_day NOT IN ( 'SAT', 'SUN' )
MINUS
SELECT
holiday_date d
FROM
hh
)
)
WHERE
rn = abs(:p_offset);
Enjoy Life!