Solved

syntax for creating inventory turns report

Posted on 2014-02-25
9
416 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 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 48

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 48

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 ?
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.
Viewers will learn how the fundamental information of how to create a table.

860 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