Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

syntax for creating inventory turns report

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
tips54
Asked:
tips54
  • 4
  • 3
  • 2
1 Solution
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
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
 
tips54Author 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
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
PortletPaulfreelancerCommented:
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
 
Andrei FomitchevCommented:
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
 
tips54Author 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
0
 
tips54Author Commented:
Thanks Formand.  your query does not give me the expected output.  I am after Inventory Turnover over the 12 month period.
0
 
Andrei FomitchevCommented:
Please, put the example of the table that you expect as output.
0
 
tips54Author Commented:
Thank you Paul. I ended up using your code with a few mods.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now