技术需求：库存台账需包含所有日期，现有SQL代码生成数据见指定表格

Got it, let's tweak your SQL so your inventory ledger shows every single date (not just those with ledger entries) — here's a revised version with step-by-step explanations:

Core Idea #

The original query only returns dates that have actual item ledger activity. To include all dates in your range, we need to:

• Generate a continuous sequence of dates covering the full span of your data
• Pair this date list with every unique inventory group (item + customer + storage type)
• Left join with your aggregated inventory data to fill in values for active dates, and default to zeros/carry forward closing stock for inactive dates

Revised SQL Code #

-- Clean up existing temp tables if they exist
IF OBJECT_ID('TEMPDB..#temp') IS NOT NULL DROP TABLE #temp
IF OBJECT_ID('TEMPDB..#temp2') IS NOT NULL DROP TABLE #temp2

-- Step 1: Extract raw ledger data with in/out quantity splits
SELECT 
    [Entry No_],
    [Storage Type],
    [Location Code],
    [Primary Customer No_],
    [Posting Date],
    [Item No_],
    Quantity,
    [Quantity in Pallets],
    CASE WHEN Quantity > 0 THEN Quantity ELSE 0 END AS [In],
    CASE WHEN Quantity < 0 THEN Quantity ELSE 0 END AS Out
INTO #temp 
FROM [XYZ$Item Ledger Entry]
-- WHERE [Primary Customer No_]='AHMP000259' -- Uncomment if you need to filter by specific customer

-- Step 2: Aggregate data by date, item, customer, and storage type
SELECT 
    [Storage Type],
    SUM([Quantity in Pallets]) AS [Quantity in Pallets],
    [Primary Customer No_],
    [Item No_],
    CAST([Posting Date] AS DATE) AS [Posting Date],
    SUM(Quantity) AS Quantity,
    SUM([In]) AS [In],
    SUM(Out) AS Out
INTO #temp2 
FROM #temp 
GROUP BY [Primary Customer No_], [Item No_], CAST([Posting Date] AS DATE), [Storage Type]

-- Step 3: Generate a full continuous date range from earliest to latest posting date
DECLARE @StartDate DATE, @EndDate DATE
SELECT @StartDate = MIN([Posting Date]), @EndDate = MAX([Posting Date]) FROM #temp2

;WITH DateRange AS (
    SELECT @StartDate AS [Date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date])
    FROM DateRange
    WHERE [Date] < @EndDate
)

-- Step 4: Capture all unique inventory groups (item + customer + storage type)
, InventoryGroups AS (
    SELECT DISTINCT 
        [Item No_], 
        [Primary Customer No_], 
        [Storage Type]
    FROM #temp2
)

-- Step 5: Combine dates with inventory groups, fill in data, and calculate closing stock
SELECT 
    dr.[Date] AS [Posting Date],
    ig.[Storage Type],
    ig.[Primary Customer No_],
    ig.[Item No_],
    ISNULL(t2.[Quantity in Pallets], 0) AS [Quantity in Pallets],
    ISNULL(t2.Quantity, 0) AS Quantity,
    ISNULL(t2.[In], 0) AS [In],
    ISNULL(t2.Out, 0) AS Out,
    -- Carry forward closing stock for dates with no activity
    SUM(ISNULL(t2.Quantity, 0)) OVER (
        PARTITION BY ig.[Item No_], ig.[Primary Customer No_], ig.[Storage Type] 
        ORDER BY dr.[Date]
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Closing
FROM DateRange dr
CROSS JOIN InventoryGroups ig
LEFT JOIN #temp2 t2 
    ON dr.[Date] = t2.[Posting Date]
    AND ig.[Item No_] = t2.[Item No_]
    AND ig.[Primary Customer No_] = t2.[Primary Customer No_]
    AND ig.[Storage Type] = t2.[Storage Type]
ORDER BY ig.[Item No_], ig.[Primary Customer No_], dr.[Date]
OPTION (MAXRECURSION 0) -- Required if your date range is longer than 100 days

Key Changes Breakdown #

• DateRange CTE: Creates every date between the first and last entry in your ledger. The MAXRECURSION 0 flag lets it handle date spans longer than 100 days.
• InventoryGroups CTE: Grabs all unique combinations of item, customer, and storage type to ensure we don't miss any inventory buckets.
• Cross Join + Left Join: Ensures every date has a row for every inventory group, even if there's no activity that day.
• ISNULL(): Fills in zero values for quantities on inactive dates so your ledger looks consistent.
• Adjusted Closing Calculation: The window function now sums all quantities (including zeros) to correctly carry forward the closing inventory across every date.

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