SQL存储过程实现：指定日期无记录时插入&补全整月缺失数据

Alright, let's work through your two SQL challenges. I'll start with the straightforward insert scenario, then dive into the more complex stored procedure for filling missing monthly data with the latest available records.

The core idea here is to first check if the record for your target date (and likely your shipper, since you have shipper-specific fields) exists, then insert only if it doesn't.

Example Implementation (Single Record) #

Assuming your table is named ShipmentData, and you want to check for a specific shipper + date combination (since multiple shippers might have records on the same date):

DECLARE @TargetDate DATE = '2018-04-24';
DECLARE @ShipperCode VARCHAR(50) = 'XYZ789';
DECLARE @ShipperName VARCHAR(100) = 'XYZ Freight';
DECLARE @DefaultVolume DECIMAL(10,2) = 0.00; -- Adjust default value as needed

-- Check if the record already exists
IF NOT EXISTS (
    SELECT 1 
    FROM ShipmentData 
    WHERE date = @TargetDate 
      AND Shippercode = @ShipperCode
)
BEGIN
    -- Insert new record if no match found
    INSERT INTO ShipmentData (date, shipperName, Shippercode, Volume)
    VALUES (@TargetDate, @ShipperName, @ShipperCode, @DefaultVolume);
END

Notes #

• If you need to insert records for all shippers on a target date (even those with no existing records that day), you'd first get a list of all unique shippers, then use a set-based insert with NOT EXISTS against each shipper-date pair (avoid loops for better performance).
• Adjust the @DefaultVolume value to match your business needs (e.g., use the last known volume instead of 0 if that makes sense).

This requires generating a full date range for the target month, pairing it with every shipper, and then filling in missing data with the most recent available record for each shipper. Below is a stored procedure implementation (we'll use SQL Server syntax here; adjust for MySQL/PostgreSQL as needed).

Stored Procedure Code #

CREATE PROCEDURE GetFullMonthlyShipmentData
    @InputDate DATE -- Pass any date in the target month, e.g., '04/24/2018'
AS
BEGIN
    SET NOCOUNT ON;

    -- Step 1: Calculate the first and last day of the target month
    DECLARE @MonthStart DATE = DATEFROMPARTS(YEAR(@InputDate), MONTH(@InputDate), 1);
    DECLARE @MonthEnd DATE = EOMONTH(@InputDate);

    -- Step 2: Generate all dates in the target month using a recursive CTE
    WITH DateRange AS (
        SELECT @MonthStart AS DateValue
        UNION ALL
        SELECT DATEADD(DAY, 1, DateValue)
        FROM DateRange
        WHERE DateValue < @MonthEnd
    ),
    -- Step 3: Get all unique shippers (include all shippers, or filter to those active in the month)
    UniqueShippers AS (
        SELECT DISTINCT Shippercode, shipperName
        FROM ShipmentData
        -- Uncomment below if you only want shippers that had records in the target month
        -- WHERE date BETWEEN @MonthStart AND @MonthEnd
    )
    -- Step 4: Combine dates and shippers, then pull in the latest available volume for each pair
    SELECT
        dr.DateValue AS date,
        us.shipperName,
        us.Shippercode,
        -- Use the day's volume if available; otherwise use the most recent prior volume
        COALESCE(sd.Volume, latest.Volume) AS Volume
    FROM DateRange dr
    CROSS JOIN UniqueShippers us
    -- Left join to get the day's actual record if it exists
    LEFT JOIN ShipmentData sd 
        ON sd.date = dr.DateValue 
        AND sd.Shippercode = us.Shippercode
    -- Get the latest volume record for the shipper on or before the current date
    OUTER APPLY (
        SELECT TOP 1 Volume
        FROM ShipmentData
        WHERE Shippercode = us.Shippercode
          AND date <= dr.DateValue
        ORDER BY date DESC
    ) latest
    ORDER BY us.Shippercode, dr.DateValue;
END

How It Works #

1. DateRange CTE: Generates every date from the start to the end of the target month, ensuring no days are missing from the result.
2. UniqueShippers CTE: Gets all distinct shippers to guarantee each one has a full month of records in the output.
3. CROSS JOIN: Creates a row for every shipper-date combination in the month—this is the foundation of our full dataset.
4. OUTER APPLY: Fetches the most recent volume record for each shipper up to the current date, which fills in gaps when a day has no existing data.
5. COALESCE: Prioritizes the day's actual volume if it exists; if not, it uses the latest available volume from prior days.

Adjustments for Other Databases #

• MySQL: Use DATE_ADD(DateValue, INTERVAL 1 DAY) instead of DATEADD, and ensure recursive CTEs are enabled (MySQL 8.0+ supports them).
• PostgreSQL: Replace DATEFROMPARTS with DATE_TRUNC('month', @InputDate), and EOMONTH with (DATE_TRUNC('month', @InputDate) + INTERVAL '1 month' - INTERVAL '1 day').
• Performance: Add indexes on (Shippercode, date) to speed up the OUTER APPLY subquery, especially if your table contains a large amount of data.

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