syntax for creating inventory turns report

tips54
tips54 used Ask the Experts™
on
Hi,

I have Three tables one for item detail,  InventoryData, and the other has InvoiceSales data. I would like to create a Inventory turns report that shows the Turns for every item over a 12 month period.  If the item has not been sold then show zero turn.
I am not sure how to generate the query to render this data.

Tables:
ItemDetail
itemID     Desc                      Item_uid
234-31     Widget x               10
234-32     Widget r               11
234-33     Widget z               12

InventoryData
 Item_uid      Qty_OH       LocationID
10                   150             100001
11                   100             100001
12                   500             100001

InvoiceSales

Invoice_no      CustID    InvoiceDate  Approved
1                       1001       10/10/13        Y
2                       1002       11/10/13        Y
3                       1001       11/15/13        Y
4                       1003       12/12/13        Y

InvoiceDetail
 Invoice_no         itemID     Desc                      Item_uid          Unit_Sold  
1                          234-31     Widget x               10                      40            
2                          234-31     Widget x               10                      50
3                          234-32     Widget z               12                      200
3                          234-31     Widget x               10                      5
4                          234-32     Widget z                12                      250

Notice: Item 234-32 has never been sold.

Expected results:

itemID     Desc    Item_uid,  Average_sold  Last_Sold        Turns
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
this result:
| ITEMID |     DESC | ITEM_UID | AVERAGE_SOLD |                       LAST_SOLD | TURNS |
|--------|----------|----------|--------------|---------------------------------|-------|
| 234-31 | Widget x |       10 |           31 | November, 15 1913 11:00:00+0000 |     3 |
| 234-32 | Widget r |       11 |       (null) |                          (null) |     0 |
| 234-33 | Widget z |       12 |          225 | December, 12 1913 11:00:00+0000 |     2 |

Open in new window

produced by this query:
SELECT

  stdet.itemID
, stdet.[Desc]
, stock.Item_uid
, avg(invdet.Unit_Sold) AS Average_sold
, max(invoice.InvoiceDate) AS Last_Sold
, count(DISTINCT invoice.Invoice_no) AS Turns

FROM InventoryData AS stock
INNER JOIN ItemDetail AS stdet ON stock.Item_uid = stdet.Item_uid
LEFT JOIN InvoiceDetail AS invdet ON stock.Item_uid = invdet.Item_uid
LEFT JOIN InvoiceSales AS invoice ON invdet.Invoice_no = invoice.Invoice_no
          AND invoice.InvoiceDate BETWEEN '19130101' AND '20140101'

GROUP BY
  stdet.itemID
, stdet.[Desc]
, stock.Item_uid
	;

see it working at: http://sqlfiddle.com/#!3/0936a/7
	

Open in new window

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
notes:
a. wasn't sure if you needed Item 234-32 listed or not. If you don't needed it then the left joins can be changed to inner joins and the date range condition could be a where clause

b. not sure what a "turn" is. I have counted the number of invoices.

Author

Commented:
I was just thinking should Turn could be wrong it   COGS / AV. Inventory .   which mean we can include a cost column in the ItemDetail table.  

http://www.investopedia.com/terms/i/inventoryturnover.asp
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Turnover.

OK, that's more understandable.
As you note you need a unit_cost field (or similar), but typically costs of inventory items will vary over time so it isn't just as simple as adding one field.

ball is in your court I believe
1. It summarizes Qty_OH from different locations
2. It calculates "Sold" for 0 sold
3. Total is "Sold" + Qty_OH
4. It selects invoices for the last 12 months
5. I created tables and the result came from the query

itemID      Desc      Item_uid           Sold      Qty_OH      Total
234-31      Widget x                 10              95            150        245
234-32      Widget r                 11                0            100        100
234-33      Widget z                 12            450            500        950

USE Items
GO
SELECT itemID, [Desc], tttt.Item_uid, Sold, Qty_OH, Sold+Qty_OH AS Total FROM (
      SELECT itemID, [Desc], ttt.Item_uid, Sum(Sold) AS Sold FROM (
            SELECT * FROM (
                  SELECT itemID, [Desc], id.Item_uid, IsNull(InvoiceDate,DateAdd(day, -1, GetDate())) AS InvoiceDate, IsNull(Sold,0) AS Sold FROM ItemDetail id
                  LEFT JOIN
                  (
                        SELECT InvoiceDate, Item_uid, Sum(Unit_Sold) AS Sold
                        FROM InvoiceSales s
                        LEFT JOIN InvoiceDetail d ON d.InvoiceNo = s.Invoice_no
                        GROUP BY InvoiceDate, Item_uid
                  ) t
                  ON t.Item_uid = id.Item_uid
            ) tt
            WHERE InvoiceDate BETWEEN DateAdd(month, -12, GetDate()) AND GetDate()
      ) ttt
      GROUP BY itemID, [Desc], Item_uid
) tttt
LEFT JOIN (
      SELECT Item_uid, Sum(Qty_OH) AS Qty_OH FROM InventoryData
      GROUP BY Item_uid
) i ON i.Item_uid = tttt.Item_uid
ORDER BY itemID

Author

Commented:
I have added the unit cost of the item at the time sold. what would the query look like now?

InvoiceDetail
 Invoice_no         itemID     Desc                      Item_uid          Unit_Sold      Unit_Cost
1                          234-31     Widget x               10                      40                  1.25
2                          234-31     Widget x               10                      50                  1.50
3                          234-32     Widget z               12                      200                3.00
3                          234-31     Widget x               10                      5                    1.25
4                          234-32     Widget z                12                     250                5.00

Author

Commented:
Thanks Formand.  your query does not give me the expected output.  I am after Inventory Turnover over the 12 month period.
Please, put the example of the table that you expect as output.

Author

Commented:
Thank you Paul. I ended up using your code with a few mods.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial