Скачать или смотреть How to Associate Each Holiday Date with the Next Workday in Oracle SQL Using Window Functions

Learn an efficient way to link each holiday date in a calendar table to its immediately following workday using Oracle SQL window functions, eliminating costly joins.
---
This video is based on the question https://stackoverflow.com/q/79538868/ asked by the user 'user30080503' ( https://stackoverflow.com/u/30080503/ ) and on the answer https://stackoverflow.com/a/79539799/ provided by the user 'Paul W' ( https://stackoverflow.com/u/20542862/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to associate the next value of a field in a table next to the field itself?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to drop me a comment under this video.
---
The Problem: Linking Holiday Dates to Their Next Workdays

In calendar datasets, it's common to mark certain dates as holidays (vacations) and others as workdays. Suppose you want to create a report or derived table that shows each holiday date next to the very next workday that follows it.

A typical approach uses a self-join with conditions to find the minimum workday coming after each holiday, but this can be inefficient, especially with large datasets.

Traditional Approach and Its Drawbacks

A straightforward method looks like this:

[[See Video to Reveal this Text or Code Snippet]]

While this finds the next workday effectively, it:

Requires an explicit join, causing performance issues on large tables.

Relies on an arbitrary date range (+ 10 days), which is a brittle heuristic.

Modern and Efficient Solution: Use Window Functions

Oracle supports powerful window functions that can simplify this task without explicit joins or subqueries.

Step-by-Step Explanation:

Retrieve all rows: Include both holidays and workdays in your base query.

Identify non-holiday dates: Use DECODE or CASE to set holiday dates to NULL and keep non-holidays as their date.

Use FIRST_VALUE with IGNORE NULLS: This function scans forward and picks the first non-null (workday) date after the current row.

Filter the results: Finally, select only the rows corresponding to holidays, now associated with their next workday.

Example Query

[[See Video to Reveal this Text or Code Snippet]]

Why This Works Better:

No explicit join: The expensive self-join is replaced by an analytic function.

Accurate next workday: The window frame from the current row to the end ensures looking ahead correctly.

Cleaner and easier to maintain: Nested queries break the problem into manageable steps.

Summary

To associate each holiday with the subsequent workday in Oracle SQL, prefer FIRST_VALUE with IGNORE NULLS over self-joins:

Build a derived column nullifying holiday dates.

Use a window function to find the next non-null date.

Filter on holidays to get your final mapping.

This method improves performance and simplifies your code while delivering precise results.