Netezza SQL实现工作日返回计数、非工作日返回NULL

Got it, let's tackle this Netezza SQL problem step by step since you're just starting out! The goal is to write a single query that checks if the latest as_of_date falls on a weekend, returns NULL if it does, and counts matching records otherwise—no scripts needed.

Approach #

We'll use a combination of:

• A subquery to grab the maximum as_of_date from your table
• The TO_CHAR() function to check the day of the week (super readable if we use the full day name)
• A CASE expression to branch between returning NULL or your record count

The Query #

Here's the ready-to-use SQL (replace placeholders with your actual table and conditions):

SELECT
    CASE
        -- Check if the latest date is Saturday or Sunday
        WHEN TO_CHAR(max_as_of_date, 'FMDAY') IN ('SATURDAY', 'SUNDAY') THEN NULL
        -- If it's a weekday, run your count with filters
        ELSE (SELECT COUNT(*) FROM your_table WHERE your_filter_conditions_here)
    END AS record_count
FROM (SELECT MAX(as_of_date) AS max_as_of_date FROM your_table) AS date_check;

Quick Explanation #

1. Subquery date_check: First, we fetch the most recent as_of_date from your table—this ensures we're checking the right date.
2. TO_CHAR() with FMDAY: This converts the date to an uppercase day name (no leading spaces, thanks to FM), making it easy to check for Saturday/Sunday without remembering numeric codes.
3. CASE Expression: This is our "if-else" logic. If the latest date is a weekend, we return NULL. If not, we run the COUNT(*) query with your specific filters to get the record count.

Alternative (Using Numeric Day Codes) #

If you prefer using numeric values (Netezza uses 1 for Sunday, 7 for Saturday), you can rewrite the CASE condition like this:

WHEN TO_CHAR(max_as_of_date, 'D') IN ('1', '7') THEN NULL

Either version works—pick whichever feels clearer to you as a beginner!

内容的提问来源于stack exchange，提问作者Poiks