SQL Server：遍历日期与价格生成商品价格的起止日期

Got it, let's break down how to solve this—you need to map each price entry (tied to a unique SFLITM/SFITM pair) to its valid start and end date range, and you're already using row_number() as a starting point. Here are two straightforward approaches, depending on whether you want to stick with your existing row number logic or use a more streamlined window function.

Approach 1: Build on Your Existing Row_Number() Partitioning #

If you want to expand on the row_number() you're already implementing, you can self-join the ranked results to link each price entry with the next one in sequence:

WITH ranked_prices AS (
    SELECT 
        SFITM,
        SFLITM,
        SFDATE AS price_start_date,
        SFMMTL AS price,
        -- Assign sequential rank to each price entry per material, ordered by effective date
        ROW_NUMBER() OVER (PARTITION BY SFLITM ORDER BY SFDATE) AS entry_rank
    FROM your_pricing_table -- Replace with your actual table name
)
SELECT 
    current.SFITM,
    current.SFLITM,
    current.price_start_date,
    -- Calculate end date as next entry's start date minus 1 day; use placeholder for final entry
    CASE 
        WHEN next.price_start_date IS NOT NULL THEN DATEADD(day, -1, next.price_start_date)
        ELSE '9999-12-31' -- Use NULL or a far-future date if the price is still active
    END AS price_end_date,
    current.price
FROM ranked_prices current
LEFT JOIN ranked_prices next
    ON current.SFLITM = next.SFLITM
    AND current.entry_rank = next.entry_rank - 1
ORDER BY current.SFLITM, current.price_start_date;

How This Works: #

• The CTE (ranked_prices) assigns a sequential number to each price entry for the same SFLITM, ordered by the effective date (SFDATE).
• We join the CTE to itself: each entry links to the next entry in the sequence (where entry_rank is one higher).
• The end date for the current price is the next entry's start date minus one day. For the final entry (no subsequent price), we use a placeholder like 9999-12-31 to indicate it's still valid.

Approach 2: Streamlined with LEAD() Window Function #

If you want to skip the self-join and use a more efficient window function, LEAD() is perfect—it lets you grab the next entry's SFDATE directly without joining:

SELECT 
    SFITM,
    SFLITM,
    SFDATE AS price_start_date,
    -- Get next entry's start date, subtract 1 day; use placeholder if no next entry exists
    COALESCE(
        DATEADD(day, -1, LEAD(SFDATE) OVER (PARTITION BY SFLITM ORDER BY SFDATE)),
        '9999-12-31'
    ) AS price_end_date,
    SFMMTL AS price
FROM your_pricing_table
ORDER BY SFLITM, price_start_date;

Notes for Different SQL Dialects: #

Date arithmetic varies slightly based on your database:

• MySQL/MariaDB: Use DATE_SUB(next.SFDATE, INTERVAL 1 DAY) instead of DATEADD
• PostgreSQL: Use next.SFDATE - INTERVAL '1 day'
• Oracle: Use next.SFDATE - 1

Key Considerations #

• Ensure SFDATE is a proper date type (not a string) to avoid arithmetic errors.
• If you have duplicate SFDATE entries for the same SFLITM, adjust the ORDER BY in the window function to include additional fields (like SFMMTL) to handle ties consistently.

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