Trouble with query. Kind of lost here

Question

Post reply

Trouble with query. Kind of lost here

romerob

Grasshopper

Points: 15
More actions
November 17, 2008 at 8:40 am

#195955

Hello,
I'm having problems achieving this:
I have a list of purchases by client. Something like:
Table name: Order
Client | Product
XXX | 001
XXX | 003
XXX | 007
XXX | 009
YYY | 001
YYY | 004
YYY | 005
YYY | 002
ZZZ | 002
ZZZ | 009
ZZZ | 008
ZZZ | 007
Then I have "product combos". For example, if you have bought products 002, 007 and 009 you have then bought all products for 'Combo A'. I have this information like this:
Table name: Combos
Product | Combo
002 | Combo A
007 | Combo A
009 | Combo A
008 | Combo B
005 | Combo B
001 | Combo B
002 | Combo B
004 | Combo C
003 | Combo C
I want to identify those clients that are near to complete the 'combo'. In this case, I would like to identify that:
client XXX has already bought two of the three products of 'Combo A', client YYY has already bought three of the four products of 'Combo B' and client ZZZ has already bought the three products of 'Combo A'.
I would need a list looking something like:
Table name: ClientCombo
Client | Combo
XXX | Combo A
YYY | Combo B
ZZZ | Combo A
Thanks for your help!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Something like this should do the trick:

[font="Courier New"]DROP TABLE #Order

CREATE TABLE #Order (Client VARCHAR(3), Product VARCHAR(3))

INSERT INTO #Order (Client, Product)

SELECT 'XXX','001' UNION ALL -- B

SELECT 'XXX','003' UNION ALL -- C

SELECT 'XXX','007' UNION ALL -- A

SELECT 'XXX','009' UNION ALL -- A

SELECT 'YYY','001' UNION ALL -- B

SELECT 'YYY','004' UNION ALL -- C

SELECT 'YYY','005' UNION ALL -- B

SELECT 'YYY','002' UNION ALL -- A,B

SELECT 'ZZZ','002' UNION ALL -- A,B

SELECT 'ZZZ','009' UNION ALL -- A

SELECT 'ZZZ','008' UNION ALL -- B

SELECT 'ZZZ','007' -- A

DROP TABLE #Combos

CREATE TABLE #Combos (Product VARCHAR(3), Combo VARCHAR(7))

INSERT INTO #Combos (Product, Combo)

SELECT '002','Combo A' UNION ALL

SELECT '007','Combo A' UNION ALL

SELECT '009','Combo A' UNION ALL

SELECT '008','Combo B' UNION ALL

SELECT '005','Combo B' UNION ALL

SELECT '001','Combo B' UNION ALL

SELECT '002','Combo B' UNION ALL

SELECT '004','Combo C' UNION ALL

SELECT '003','Combo C'

SELECT *

FROM #Order o

INNER JOIN #Combos c ON c.Product = o.Product

ORDER BY o.Client, o.Product

SELECT o.Client, c.Combo, d.ComboProductsMax, COUNT(*) AS ComboProductsFound

FROM #Order o

INNER JOIN #Combos c ON c.Product = o.Product

INNER JOIN (SELECT Combo, COUNT(*) AS ComboProductsMax FROM #Combos GROUP BY Combo) d ON d.Combo = c.Combo

GROUP BY o.Client, c.Combo, ComboProductsMax

HAVING ComboProductsMax - COUNT(*) < 2

ORDER BY o.Client, c.Combo

[/font]

You might want to wrap this in another SELECT to pick the "most promising" product group per customer.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

romerob Grasshopper Points: 15 More actions · Answer 2

This is great Chris. Thanks a lot.

I knew there was an elegant way to solve it.

Cheers,

Bromero