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!

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  November 17, 2008 at 9:21 am

  #899386

  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

  November 17, 2008 at 9:55 am

  #899423

  This is great Chris. Thanks a lot.

  I knew there was an elegant way to solve it.

  Cheers,

  Bromero