Starting with the SQL view for last sale by customer and item by Victoria Yudin with a couple modifications. I created some queries to recursively capture all of the components associated with the items sold as well and where they are found.
This is the modified view based on Victoria’s Item Last sold Per Customer
SELECT MAX(H.DOCDATE) AS Last_Sale, D.ITEMNMBR, I.ITEMTYPE, FROM --this is simple view combining work and history dbo.SOP10100_SOP30200 AS H INNER JOIN dbo.SOP10200_SOP30300 AS D ON H.SOPTYPE = D.SOPTYPE AND H.SOPNUMBE = D.SOPNUMBE LEFT OUTER JOIN dbo.IV00101 AS I ON D.ITEMNMBR = I.ITEMNMBR --looking for orders change SOPTYPE to 3 for Invoices WHERE (H.SOPTYPE = 2) AND (H.VOIDSTTS = 0) GROUP BY D.ITEMNMBR, I.ITEMNMBR, I.ITEMTYPE
The above query will pull out all top level items that have been sold and the last sale date. I made this a view to pull against.
Now the key to understanding exactly what inventory you have sold and when you sold it, requires you dive into the BOMs of an item. Now often items are used on multiple BOMs. It can also be helpful to know what it is found in for those components that no one remembers are even in the system.
This is the query I came up with to accomplish that. It is surprisingly efficient too or at least I think it is for what it is doing.
declare @Months int set @Months = 12 --create temp table to store the items Create table #TEMPINVALL ( Lastsale datetime, ITEM varchar(50), FOUNDIN varchar(50), ITEMTYPE varchar(10), COST varchar(50), COMPLEVEL int ) insert into #TEMPINVALL --top level SELECT Last_Sale, dj.ITEMNMBR,'' as 'FOUNDIN', dj.ITEMTYPE, STNDCOST, 0 FROM _DJ_LASTSALEDATEPERITEM dj inner join IV00101 iv on iv.ITEMNMBR = dj.ITEMNMBR --pass number of months back or remove for all items ever WHERE (Last_Sale > DATEADD(m, - @Months, GETDATE())) --create temp table to store component information create table #tablecomps ( ITEM varchar(50), FOUNDIN varchar(50), BOMEBLEVEL int, Lastsale datetime) --create fetch and pass items into the BOM tables --to recursively pull out additonal components declare @ITEM varchar(50) declare @lastsale datetime declare @FOUNDIN varchar(50) declare itemcomp Cursor For select ITEM, Lastsale from #TEMPINVALL Open itemcomp fetch Next from itemcomp into @ITEM, @lastsale; while @@Fetch_status = 0 begin With BOM_No (CPN_I, PPN_I, BOM_LEVEL) AS (SELECT DISTINCT CPN_I, PPN_I, 0 FROM BM010115 WHERE PPN_I = @ITEM UNION ALL SELECT BM010115.CPN_I, BM010115.PPN_I, BOM_LEVEL + 1 FROM BOM_No INNER JOIN BM010115 ON BM010115.PPN_I = BOM_No.CPN_I) insert into #tablecomps SELECT distinct CPN_I, PPN_I, BOM_LEVEL, @lastsale FROM BOM_No OPTION (MaxRecursion 100) fetch Next from itemcomp into @ITEM, @lastsale; end close itemcomp deallocate itemcomp --combine the two temp tables with a union select distinct MAX(Lastsale) as Lastsale, ITEM, '' as 'FOUNDIN', case when ITEMTYPE = 1 then 'Sales Inventory' when ITEMTYPE = 2 then 'Discontinued' when ITEMTYPE = 3 then 'Kit' when ITEMTYPE = 4 then 'Misc Charges' when ITEMTYPE = 5 then 'Services' when ITEMTYPE = 6 then 'Flat Fee' End as 'ITEMTYPE' , COST, COMPLEVEL, QTYONHND from #TEMPINVALL inner join IV00102 q on ITEM = q.ITEMNMBR and RCRDTYPE = 1 group by ITEM,FOUNDIN, ITEMTYPE, COMPLEVEL, QTYONHND, COST union select distinct MAX(Lastsale) as Lastsale, ITEM, FOUNDIN, case when ITEMTYPE = 1 then 'Sales Inventory' when ITEMTYPE = 2 then 'Discontinued' when ITEMTYPE = 3 then 'Kit' when ITEMTYPE = 4 then 'Misc Charges' when ITEMTYPE = 5 then 'Services' when ITEMTYPE = 6 then 'Flat Fee' End as 'ITEMTYPE' ,STNDCOST, BOMEBLEVEL + 1, QTYONHND from #tablecomps inner join IV00101 i on ITEM = i.ITEMNMBR inner join IV00102 q on ITEM = q.ITEMNMBR and RCRDTYPE = 1 group by ITEM,FOUNDIN, ITEMTYPE, BOMEBLEVEL, QTYONHND, STNDCOST --finally drop temp tables drop table #tablecomps drop table #TEMPINVALL
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.