SQL Server新手求助：如何查询并展示期初/期末库存（表格形式）

Hey there! Let's walk through this step by step since you're new to SQL Server. First, let's make sure we're on the same page with your requirements:

• Pull transactions between 11-04-2018 and 12-04-2018
• Add an Op.St column showing the opening stock (total inventory before 11-04-2018) for each item
• Output should include all original transaction fields plus the opening stock

First, Let's Define the Inventory Logic #

From your sample data, we can infer:

• DC = 'C' means stock in (add Qty to inventory)
• DC = 'D' means stock out (subtract Qty from inventory)
• Opening Stock = Sum of all stock in - Sum of all stock out for the item before your start date (11-04-2018)

SQL Query for Your Requirement #

Here's a beginner-friendly query with comments explaining each part—just replace YourTableName with your actual table name:

-- Step 1: Calculate opening stock for each item before the query start date
WITH OpeningStock AS (
    SELECT 
        ItemName,
        -- Net stock: add incoming (C) quantities, subtract outgoing (D) quantities
        SUM(CASE WHEN DC = 'C' THEN Qty ELSE -Qty END) AS Op_St
    FROM 
        YourTableName
    WHERE 
        TDate < '2018-04-11' -- All transactions before 11-04-2018
    GROUP BY 
        ItemName
)

-- Step 2: Join opening stock to your target transaction range
SELECT 
    t.Ttype,
    t.ItemName,
    t.TDate,
    t.DC,
    t.Qty,
    os.Op_St AS [Op.St] -- Match your desired output column name
FROM 
    YourTableName t
LEFT JOIN 
    OpeningStock os ON t.ItemName = os.ItemName
WHERE 
    t.TDate BETWEEN '2018-04-11' AND '2018-04-12' -- Your target date range
ORDER BY 
    t.TDate, t.Ttype;

Expected Output #

Based on your sample data, here's what the result will look like:

Op.St Calculation Verification

Let's confirm the opening stock manually to be sure:

• Transactions before 11-04-2018:
  • LGR VANILA 08-04-2018 C 10 → +10
  • GRN VANILA 08-04-2018 C 10 → +10
  • GRN VANILA 09-04-2018 C 20 → +20
  • DA VANILA 10-04-2018 D 10 → -10
  • DA VANILA 10-04-2018 D 10 → -10
• Total net stock: 10+10+20-10-10 = 20 → That's your opening stock!

Bonus: Add Closing Stock (Cl.St) #

If you also want to track inventory after each transaction, modify the query to include a running total:

WITH OpeningStock AS (
    SELECT 
        ItemName,
        SUM(CASE WHEN DC = 'C' THEN Qty ELSE -Qty END) AS Op_St
    FROM 
        YourTableName
    WHERE 
        TDate < '2018-04-11'
    GROUP BY 
        ItemName
),
TransactionWithRunningTotal AS (
    SELECT 
        t.Ttype,
        t.ItemName,
        t.TDate,
        t.DC,
        t.Qty,
        os.Op_St,
        -- Calculate cumulative net change for transactions in the date range
        SUM(CASE WHEN t.DC = 'C' THEN t.Qty ELSE -t.Qty END) 
            OVER (PARTITION BY t.ItemName ORDER BY t.TDate) AS Running_Change
    FROM 
        YourTableName t
    LEFT JOIN 
        OpeningStock os ON t.ItemName = os.ItemName
    WHERE 
        t.TDate BETWEEN '2018-04-11' AND '2018-04-12'
)

SELECT 
    *,
    Op_St + Running_Change AS [Cl.St] -- Closing stock after each transaction
FROM 
    TransactionWithRunningTotal
ORDER BY 
    TDate, Ttype;

This adds a Cl.St column showing:

• 30 after the 11-04 GRN (20 + 10)
• 20 after the 12-04 DA (30 - 10)

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