如何创建存储过程来计算年初库存，库存，缺货，库存余额在SQL Server 2005中

Question

我有3个表：

• Product（Product_Model, Product_Color, Product_Code）
• StockIn（StockIn_Date, Product_Code, Product_SerialNo）
• StockOut（StockOut_Date, Product_SerialNo）

我想创建一个存储过程来生成报告requiremen下面吨字段：

• （开始stock，stock in，stock out，stock balance）
• 组由（Product_Model，Product_Color，Product_Code）
• 过滤器的2个参数：@StartDate和@EndDate

如何使用此方案创建存储过程？

Answer 1

假设你的表StockIn和缺货有一列金额，而不是问题是毫无意义的。

因此，丑陋的和简单的查询是：

SELECT 
    BeginningStockIn 
    IsNull(BeginningStockIn.Amount, 0)-IsNull(BeginningStockOut.Amount, 0) BeginningStock, 
    IsNull(PeriodStockIn.Amount, 0) StockIn, 
    IsNull(PeriodStockOut.Amount, 0) StockOut, 
    IsNull(BeginningStockIn.Amount, 0)-IsNull(BeginningStockOut.Amount, 0)+IsNull(PeriodStockIn.Amount, 0)-IsNull(PeriodStockOut.Amount, 0) StockBalance 
FROM Product 
LEFT JOIN 
(
    SELECT 
    SUM(Amount) Amount, 
    Product_Code 
    FROM StockIn 
    WHERE StockIn_Date < @StartDate 
) BeginningStockIn ON BeginningStockIn.Product_Code = Product.Product_Code 
LEFT JOIN 
(
    SELECT 
    SUM(Amount) Amount, 
    Product_Code 
    FROM StockOut 
    WHERE StockOut_Date < @StartDate 
) BeginningStockOut ON BeginningStockOut.Product_Code = Product.Product_Code 
LEFT JOIN 
(
    SELECT 
    SUM(Amount) Amount, 
    Product_Code 
    FROM StockIn 
    WHERE StockIn_Date >= @StartDate AND StockIn_Date < @EndDate 
) PeriodStockIn ON PeriodStockIn .Product_Code = Product.Product_Code 
LEFT JOIN 
(
    SELECT 
    SUM(Amount) Amount, 
    Product_Code 
    FROM StockOut 
    WHERE StockOut_Date >= @StartDate AND StockOut_Date < @EndDate 
) PeriodStockOut ON PeriodStockOut.Product_Code = Product.Product_Code 

而且答案 - 创造您必须使用CREATE PROCEDURE语句描述here

CREATE PROC YourProcName 
    @StartDate datetime, 
    @EndDate datetime 
AS 
BEGIN 
    SET NOCOUNT ON; 

    the query 

END 

Answer 2

小时的存储过程，

所有功劳归于Oleg Dok谁发布了上面的脚本。

这里是相同的数据我一起工作：

---

这里是代码：

declare @startdate date = '2012-01-02' 
declare @enddate date = '2012-01-31' 

SELECT 
    Product.Product_Code, 
    Product.Product_Color, 
    Product.Product_Model, 
    IsNull(BeginningStockIn.Amount, 0)-IsNull(BeginningStockOut.Amount, 0) BeginningStock, 
    IsNull(PeriodStockIn.Amount, 0) StockIn, 
    IsNull(PeriodStockOut.Amount, 0) StockOut, 
    IsNull(BeginningStockIn.Amount, 0)-IsNull(BeginningStockOut.Amount, 0)+IsNull(PeriodStockIn.Amount, 0)-IsNull(PeriodStockOut.Amount, 0) StockBalance 
FROM Product 
LEFT JOIN 
(
    SELECT 
    SUM(Amount) Amount, 
    Product_Code 
    FROM StockIn 
    WHERE StockIn_Date < @StartDate 
    group by Product_Code 
) BeginningStockIn ON BeginningStockIn.Product_Code = Product.Product_Code 
LEFT JOIN 
(
    SELECT 
    SUM(Amount) Amount, 
    Product_Code 
    FROM StockOut 
    WHERE StockOut_Date < @StartDate 
    group by Product_Code 
) BeginningStockOut ON BeginningStockOut.Product_Code = Product.Product_Code 
LEFT JOIN 
(
    SELECT 
    SUM(Amount) Amount, 
    Product_Code 
    FROM StockIn 
    WHERE StockIn_Date >= @StartDate AND StockIn_Date < @EndDate 
    group by Product_Code 
) PeriodStockIn ON PeriodStockIn .Product_Code = Product.Product_Code 
LEFT JOIN 
(
    SELECT 
    SUM(Amount) Amount, 
    Product_Code 
    FROM StockOut 
    WHERE StockOut_Date >= @StartDate AND StockOut_Date < @EndDate 
    group by Product_Code 
) PeriodStockOut ON PeriodStockOut.Product_Code = Product.Product_Code 

这里是输出：

Product_Code Product_Color Product_Model BeginningStock StockIn StockOut StockBalance 
1 red 123 5 0 3 2 
2 red 456 10 0 3 7 

是这是你想要的吗？