Amazon's "Customers who bought this item also bought..."

This set of views is designed to show related items purchased with an inventory item in Dynamics GP.
This is similar in concept to Amazon's "Customers who bought this item also bought..."
The final view should ideally be restricted to a single item and will return items that have been bought (invoiced) with the
restricted item and the % of times they were bought together.

Using Fabrikam as a example, if you run this for item '5-Fee', you'll see that item '5-TVLLABOR' was on the same invoice as '5-Fee' 56%
of the time. The goal is to find related products for suggestive selling or product positioning.

These were built as views to allow them to be built into a Smartlist Builder based Smartlist. The use of views means that performance
suffers because the view has to hold all of the combinations and it may slow down with a large number of items and transactions.

I also have a Stored Procedure at http://www.dynamicaccounting.net that does the same thing and is more efficient.
That is a better option for custom programming, Crystal Reports or SSRS.

*/


--Create view to build denominator for % calc. This is the number of invoices per item.
Create View RelatedItems_InvoiceCount as
Select ItemNmbr as ItemNumber, COUNT(SOPNumbe) as InvoiceCount
FROM sop30300
WHERE sopnumbe IN (SELECT sopnumbe FROM SOP30300) and SOPTYPE=3
Group by ITEMNMBR

--Create view to hold the the relationships. This holds other items on the same invoice as an item.
Create View RelatedItems_Relationship as
SELECT SOP30300.SOPTYPE, SOP30300.SOPNUMBE, SOP30300.ITEMNMBR, SOP30300_2.ITEMNMBR AS RelatedItem
FROM SOP30300 INNER JOIN
SOP30300 AS SOP30300_2 ON SOP30300.SOPTYPE = SOP30300_2.SOPTYPE AND SOP30300.SOPNUMBE = SOP30300_2.SOPNUMBE
WHERE (SOP30300.SOPNUMBE IN
(SELECT SOPNUMBE
FROM SOP30300 AS SOP30300_1)) AND (SOP30300.SOPTYPE = 3) and SOP30300.ITEMNMBR <>SOP30300_2.ITEMNMBR

GROUP BY SOP30300.SOPTYPE, SOP30300.SOPNUMBE, SOP30300.ITEMNMBR, SOP30300_2.SOPNUMBE, SOP30300_2.SOPTYPE, SOP30300_2.ITEMNMBR
order by itemnmbr


--Create view to hold both related items and the % of common invoices they appeared on
Create View RelatedItems_PcntAlsoBought as
SELECT RelatedItems_Relationship.itemnmbr as ItemNumber, RelatedItems_Relationship.RelatedItem AS RelatedItem, IV00101.ITEMDESC AS Description,
Cast(cast(COUNT(SOPNUMBE) as decimal(8,2))/ CAST( RelatedItems_InvoiceCount.InvoiceCount as decimal(8,2)) *100 as decimal(8,2)) AS PcntAlsoBought
FROM RelatedItems_Relationship INNER JOIN
IV00101 ON RelatedItems_Relationship.RelatedItem = IV00101.ITEMNMBR
INNER JOIN
RelatedItems_InvoiceCount ON RelatedItems_Relationship.ItemNmbr = RelatedItems_InvoiceCount.ItemNumber
Where RelatedItems_Relationship.ITEMNMBR in (Select ITEMNMBR from IV00101)
GROUP BY RelatedItems_Relationship.itemnmbr,RelatedItems_Relationship.Relateditem, IV00101.ITEMDESC, RelatedItems_InvoiceCount.InvoiceCount

--Execute the view limiting it to one item and sorting by highest %
Select * from RelatedItems_PcntAlsoBought where ItemNumber='5-Fee'
Order by 4 desc