title: T-SQL Query to Calculate Out Of Stock
body: <p>The end goal is to calculate Adjusted Daily Volume for new product.</p>
  
  <p>First I need to know when an item is out of stock, that is what is killing me.</p>
  
  <pre><code>TransaDate  ItemCode    TransQty    OnHandBefore    OnHandAfter
  1/1/2016    xyz-125       -5              20               15
  1/4/2016    xyz-126       -4              15               11
  1/7/2016    xyz-127       -2              11                9
  1/8/2016    xyz-128       -6               9                3
  1/8/2016    xyz-129       -3               3                0
  1/13/2016   xyz-130       20               0               20
  1/13/2016   xyz-131       -4              20               16
  1/13/2016   xyz-132      -10              16                6
  1/18/2016   xyz-133       -6               6                0
  1/23/2016   xyz-134        5               0                5
  1/26/2016   xyz-135       -5               5                0
  1/28/2016   xyz-136       20               0               20
  1/28/2016   xyz-137       -4              20               16
  </code></pre>
  
  <p>Anytime an OnHandAfter is zero I want to consider the item out of stock that entire day. Ideally I would ignore days where there is 0 Before or After since those are both partial sales. But the important, and hard part for me, is the days after an item goes out of stock. There are no entries so I can't do a straight join. So in the above example the item is out of stock on the 26th and on the 27th, even though the 27th isn't listed. In total this example shows Jan 8,9,10,11,12,13 as out of stock, then we receive 20 into stock, then Jan 18,19,20,21,22,23,26,27,28 are also out of stock.</p>
  
  <p>Total Sold: 49<br>
  Average Daily Volume: 49/31 = 1.58<br>
  Days Out-Of-Stock: 15<br>
  Adjusted Daily Volume: 49/(31-15) = 3.06</p>
  
  <p>Since no records are made for days with no sales I think I need to create a derived table (or something like that) that contains all the dates in the range. To make it more complicate the date range will be dynamic for each item, I need to know how many are sold in the first 30 days the item went live on our site. So the start date needs to be dynamic for each item.</p>
  
  <p>The other main table is the item table. I would show what I have so far but it would be know help at all. I am at a complete loss as to the best way to structure this. </p>
  
  <p>Here is my best, but sadly incomplete and sorry attempt, at showing what I want and how I think I might get to it.</p>
  
  <pre><code>With CTE_FirstSaleDate as 
  (
   SELECT MIN(TransDate) as FirstSoldOn FROM InventoryTransaction
  ), 
  WITH CTE_AllDatesTable
  AS
  (
    SELECT FirstSoldOn AS [date]
    UNION ALL
    SELECT DATEADD(dd, 1, [date])
    FROM CTE_DatesTable
    WHERE DATEADD(dd, 1, [date]) &lt; DATEADD(dd, 30, FirstSoldOn)
  ),
  CTE_OOS as
  (
  SELECT TOP 1 i.ItemCode, it.TransactionDate
  FROM InventoryTransaction it
  WHERE ( it.OnHandAfter = 0 )
  ORDER BY it.TransactionDate DESC
  )
  Select i.ItemCode, DaysCount, DaysOOSCount, AvgDlyVol, AdjDlyVol 
  From Items i
  Join InventoryTransaction...
  </code></pre>
  
  <p>Any help regarding how I can get a bunch of tables, dynamic dates that aren't in tables and all this other stuff together will be greatly appreciated. Sure it would be awesome if there is one magical script but this will be run once per month so breaking it down is fine. </p>