syntax for creating inventory turns report

Posted on 2014-02-25
Last Modified: 2014-02-27

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.

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

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


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

 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
Question by:tips54
  • 4
  • 3
  • 2
LVL 48

Accepted Solution

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:

, 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'

, stdet.[Desc]
, stock.Item_uid

see it working at:!3/0936a/7

Open in new window

LVL 48

Expert Comment

ID: 39887863
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 Comment

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.
LVL 48

Expert Comment

ID: 39888019

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.


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
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
      SELECT Item_uid, Sum(Qty_OH) AS Qty_OH FROM InventoryData
      GROUP BY Item_uid
) i ON i.Item_uid = tttt.Item_uid

Author Comment

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

 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 Comment

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

Expert Comment

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

Author Closing Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parse field in SQL View 15 96
Help writing a query 6 69
SQL Syntax join to include values from first table 3 25
Azure SQL Insert not working suddenly 11 18
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

948 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now