Скачать или смотреть Extracting the Last Working Day of the Month in BigQuery

Learn how to accurately determine the last working day of each month in BigQuery while excluding weekends with easy-to-follow SQL queries.
---
This video is based on the question https://stackoverflow.com/q/72951204/ asked by the user 'Sara' ( https://stackoverflow.com/u/9617820/ ) and on the answer https://stackoverflow.com/a/72951487/ provided by the user 'Mohammad' ( https://stackoverflow.com/u/15959787/ ) 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: Extract last working day of every month in bigquery

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 write me at vlogize [AT] gmail [DOT] com.
---
Introduction

In the world of data analysis, accurately determining the last working day of each month is a common challenge, especially when working with date functions in SQL or Google BigQuery. If you're looking to generate reports, manage payroll, or analyze financial data, knowing the correct last working day—while excluding weekends—can be crucial.

In this guide, we'll explore how to extract the last working day of every month in BigQuery. We'll review a common issue users face while performing this task and provide a solution that's both effective and simple to understand.

The Problem: Getting Last Working Days in BigQuery

Many users have attempted to use existing SQL queries to extract the last working days of the month, usually relying on elementary date subtraction. For example, the use of DATE_SUB alongside GENERATE_DATE_ARRAY can yield results, but often it does not account for weekends.

Consider the following SQL snippet meant to fetch dates:

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

This query, while functional, resulted in outputs that included weekends—something that was explicitly needed to be excluded.

Outputs from the Initial Query

The query returned the following results:

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

However, the expected output should replace weekends with the last preceding weekday:

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

As you can see, Saturdays and Sundays were included, leading to incorrect data representation.

The Solution: SQL Query for Last Working Day Extraction

To solve the issue of weekend days appearing in the results, we can enhance our SQL code. The following adjusted query accounts for Saturdays and Sundays:

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

Breakdown of the Code

Date Generation:

We generate a series of dates using GENERATE_DATE_ARRAY, which forms the basis for our analysis.

Conditional Logic:

We utilize a CASE statement to assess whether the adjusted date (one day before the end of the month) is a Saturday or Sunday.

Depending on the day identified, you subtract either 1 or 2 days from the date to find the last working day.

Ordering Results:

The final output is neatly organized in ascending order based on the last working day (d1).

Conclusion

By strategically using BigQuery and SQL's powerful date functions, you can accurately determine the last working day of each month, all while excluding weekends. This approach is especially valuable for analysts and data professionals working in financial reporting, HR-related tasks, or any scenario where understanding the timeline of working days is vital.

Stay tuned for more actionable insights and methods for effective data handling in BigQuery!