Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

syntax for creating inventory turns report

Posted on 2014-02-25
9
Medium Priority
?
474 Views
Last Modified: 2014-02-27
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
0
Comment
Question by:tips54
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39887861
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

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39887863
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.
0
 

Author Comment

by:tips54
ID: 39887900
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
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 49

Expert Comment

by:PortletPaul
ID: 39888019
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
0
 
LVL 8

Expert Comment

by:Andrei Fomitchev
ID: 39888136
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
0
 

Author Comment

by:tips54
ID: 39888755
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
0
 

Author Comment

by:tips54
ID: 39888766
Thanks Formand.  your query does not give me the expected output.  I am after Inventory Turnover over the 12 month period.
0
 
LVL 8

Expert Comment

by:Andrei Fomitchev
ID: 39891209
Please, put the example of the table that you expect as output.
0
 

Author Closing Comment

by:tips54
ID: 39893969
Thank you Paul. I ended up using your code with a few mods.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question