StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>H,</p> <p>All credit to Oleg Dok who posted the script above.</p> <p>Here is the same Data I am working with:</p> <p><img src="https://i.stack.imgur.com/i9I1Z.png" alt="enter image description here"></p> <hr> <p>Here is the Code:</p> <pre><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 </code></pre> <p>Here is the Output:</p> <pre><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 </code></pre> <p>Is this what you wanted?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload