title: SQL Monitoring Increase and decrease for the Quantity of a Product
body: <p>I am writing a simple application in VB.net and I am stuck in one SQL statement. Lets say that I have the following tables with some sample data</p>
  
  <p><strong>Products Table</strong> with <code>attributes</code> , <code>ProductID</code>  <code>ProductName</code> ,  <code>CategoryID</code> , <code>UnitPrice</code>, <code>QuantityOnhand</code>   </p>
  
  <pre><code>ProductId  Name  UnitPrice  QTYonaHand
  001        XYZ   3          25
  </code></pre>
  
  <p>.  I want to track increase and decrease for  the Quantity of items that i have. those are Some of the Transactions that make The QuantityOnhand of a Product to increase or decrease.</p>
  
  <p><strong>BillDetails Table</strong> with attributes <code>BillNo</code> , <code>Productid</code> , <code>Qty</code> , <code>Price</code> -  represents purchases from vendors and will increase quantity</p>
  
  <pre><code>  BillNo   ProductId    Qty     Price
      01      001          36      3
  </code></pre>
  
  <p><strong>InvoiceDetails Table</strong> with attributes <code>InvoiceId</code> ,<code>Productid</code> , <code>Qty</code> , <code>Price</code> -  represents sales to customers and will decrease quantity.</p>
  
  <pre><code> InvoiceNo     ProductId        Qty    Price
      10             001          6      4
      12             001          5      4.5
  </code></pre>
  
  <p>My goal is to write a SQL statement which returns table</p>
  
  <p><strong>ProductDetail</strong></p>
  
  <p>For Example Product XYZ</p>
  
  <pre><code>Type      Date         Ref     Qty        OnHand
  Bill      4/15/2017    01      36         36
  Invoice   4/16/2017    10      -6         30
  Invoice   4/17/2017    12      -5         25
  </code></pre>
  
  <p>Thanks in advance!</p>