How to create stored procedure to calculate beginning stock, stock in, stock out, stock balance in SQL Server 2005

Question

I have 3 tables:

• Product (Product_Model, Product_Color, Product_Code)
• StockIn (StockIn_Date, Product_Code, Product_SerialNo)
• StockOut (StockOut_Date, Product_SerialNo)

I want to create a stored procedure to generate report with requirement fields below:

• (beginning stock, stock in, stock out, stock balance)
• Group by (Product_Model, Product_Color, Product_Code)
• Filter by 2 parameters: @StartDate and @EndDate

How to create a stored procedure with this scenario?

Answer 1

H,

All credit to Oleg Dok who posted the script above.

Here is the same Data I am working with:

---

Here is the Code:

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

Here is the Output:

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

Is this what you wanted?

Answer 2

Assuming that your tables StockIn and StockOut have a column Amount, instead the question is senseless.

So, the ugly and the simple query is:

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

And the answer - to create the stored procedure you have to use CREATE PROCEDURE statement as described here

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

  the query

END