Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle) Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, June 13, 2014 11:10 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 8:45 AM Points: 42,066, Visits: 39,447
 Ed Wagner (6/13/2014)As always, an excellent article, Jeff. Then again, we've come to expect nothing less.Thanks, Ed. Better than the article, though, look at the great discussions going on. Lotsa good people with good ideas. That's why I love this place. Ya just gotta love this community! --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." Helpful Links:How to post code problemsHow to post performance problems
Post #1580659
 Posted Friday, June 13, 2014 4:19 PM
 SSCrazy Group: General Forum Members Last Login: Saturday, November 19, 2016 1:44 PM Points: 2,271, Visits: 5,545
 Do we have a readymade 10 million/1million row generator for this problem?
Post #1580746
 Posted Friday, June 13, 2014 4:35 PM
 SSCrazy Group: General Forum Members Last Login: Saturday, November 19, 2016 1:44 PM Points: 2,271, Visits: 5,545
 Now that we have new analytical functions, just wanted to jump in on the new windowed functions fun :)`; WITH CTE AS(SELECT P.CustomerID ,P.ProductCode , CODE = CASE WHEN ( P.ProductCode = 'B' AND LAG (P.ProductCode, 1, 0) OVER (PARTITION BY P.CustomerID ORDER BY P.ProductCode) = 'A' AND LEAD (P.ProductCode, 1, 0) OVER (PARTITION BY P.CustomerID ORDER BY P.ProductCode) <> 'C' ) THEN 1 ELSE 0 ENDFROM #Purchase PWHERE P.ProductCode in ('A','B', 'C')GROUP BY P.CustomerID ,P.ProductCode )SELECT CustomerID FROM CTE `
Post #1580750
 Posted Friday, June 13, 2014 4:48 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 8:45 AM Points: 42,066, Visits: 39,447
 ColdCoffee (6/13/2014)Now that we have new analytical functions, just wanted to jump in on the new windowed functions fun :)`; WITH CTE AS(SELECT P.CustomerID ,P.ProductCode , CODE = CASE WHEN ( P.ProductCode = 'B' AND LAG (P.ProductCode, 1, 0) OVER (PARTITION BY P.CustomerID ORDER BY P.ProductCode) = 'A' AND LEAD (P.ProductCode, 1, 0) OVER (PARTITION BY P.CustomerID ORDER BY P.ProductCode) <> 'C' ) THEN 1 ELSE 0 ENDFROM #Purchase PWHERE P.ProductCode in ('A','B', 'C')GROUP BY P.CustomerID ,P.ProductCode )SELECT CustomerID FROM CTE `What's the performance look like compared to the other methods? --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." Helpful Links:How to post code problemsHow to post performance problems
Post #1580755
 Posted Friday, June 13, 2014 4:51 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 8:45 AM Points: 42,066, Visits: 39,447
Post #1580757
 Posted Monday, June 16, 2014 8:50 PM
 SSCrazy Group: General Forum Members Last Login: Today @ 9:16 AM Points: 2,941, Visits: 3,704
 Thanks, Jeff, for this great article.I have one small editorial comment.In the section Filtering for "2" Products, the query is the one below:`--===== Find Customers that bought either "A" OR "B" -- and count the DISTINCT number of products each bought. -- The filter in the WHERE clause causes an error here. SELECT CustomerID, FROM #Purchase WHERE ProductCode IN ('A','B') AND COUNT(DISTINCT ProductCode) = 2 GROUP BY CustomerID;`The error is supposed to be Msg 147, Level 15, State 1, Line 1.... But the error returned when the code above is run is: Msg 156, Level 15, State 1, Line 5Incorrect syntax near the keyword 'FROM'.Because, I think, of the comma after CustomerID in the SELECT clause.Not a huge deal, but given how precise your articles and comments are, I think it will help those following the steps.Thanks as always for such detailed and informative work!!-webrunner -------------------"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script"Operator! Give me the number for 911!" - Homer Simpson"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #1582008
 Posted Tuesday, June 17, 2014 2:45 PM
 SSCrazy Group: General Forum Members Last Login: Saturday, November 19, 2016 1:44 PM Points: 2,271, Visits: 5,545
 Jeff Moden (6/13/2014)ColdCoffee (6/13/2014)Do we have a readymade 10 million/1million row generator for this problem?Heh... apparently, you didn't read the article where is says... The code above isn't adequate for performance testing. For those that want to explore and compare solutions of their own, I've attached code to build a million row test table at the bottom of this article in the "Resources" link. I read the article when ti was first published. after that, i was following the discussion When i ran the code, the logical reads were less but the elapsed time was higher than mister.magoo's code. i dint do a extensive testing
Post #1582601

 Permissions