Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««7891011»»»

Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle) Expand / Collapse
Author
Message
Posted Saturday, March 31, 2012 1:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:14 PM
Points: 35,266, Visits: 31,756
Dean Cochrane (3/30/2012)
Berl (3/30/2012)

Although CPU time difference is not very great, the difference is much bigger for the elapsed time.
[b]


Elapsed time includes wait time and is not a good measure of performance.


Actually, it is. The wait time you speak of also includes I/O time and that can be a major part of a performance problem. I do agree, though, that you really need to take "time to display" out of the picture because it will severly mask problems. It takes a certain amount of time to display a million rows and can make differences between two methods seem much more trivial than they might be.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1276216
Posted Saturday, March 31, 2012 10:43 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, June 7, 2014 2:41 PM
Points: 225, Visits: 47
Thank you Jeff.
Post #1276296
Posted Monday, April 2, 2012 5:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 5, 2014 12:02 AM
Points: 315, Visits: 344
Imho
SELECT DISTINCT CustomerID
FROM [#Purchase]
WHERE (ProductCode IN ('A','B')) AND (CustomerID NOT IN
(SELECT DISTINCT CustomerID
FROM [#Purchase]
WHERE (ProductCode = 'C')))
GROUP BY CustomerID, ProductCode
HAVING (COUNT(CustomerID) = 1)

Regards
Mike
Post #1276562
Posted Monday, April 2, 2012 6:31 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 22, 2014 8:23 AM
Points: 338, Visits: 1,429
michal.lisinski (4/2/2012)
Imho
SELECT DISTINCT CustomerID
FROM [#Purchase]
WHERE (ProductCode IN ('A','B')) AND (CustomerID NOT IN
(SELECT DISTINCT CustomerID
FROM [#Purchase]
WHERE (ProductCode = 'C')))
GROUP BY CustomerID, ProductCode
HAVING (COUNT(CustomerID) = 1)

Regards
Mike

This also will select customers who have purchased just one of 'A' or 'B' and not purchased 'C'. the requirement was that they should have purchased both 'A' and 'B' and not purchased 'C'.
Post #1276581
Posted Monday, April 2, 2012 6:35 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Friday, September 26, 2014 11:48 AM
Points: 8,370, Visits: 742
Jeff Moden (3/31/2012)
Dean Cochrane (3/30/2012)
Berl (3/30/2012)

Although CPU time difference is not very great, the difference is much bigger for the elapsed time.
[b]


Elapsed time includes wait time and is not a good measure of performance.


Actually, it is. The wait time you speak of also includes I/O time and that can be a major part of a performance problem. I do agree, though, that you really need to take "time to display" out of the picture because it will severly mask problems. It takes a certain amount of time to display a million rows and can make differences between two methods seem much more trivial than they might be.

Well depends, the elapsed time can an ok performance indicator, however when you run the queries for testing back to back in the same batch without clearing the buffers you may get a false sense of performance from elapsed time and many other stats. I have a local test SQL Server which I always test by stopping the instance then starting so I can be sure everything is clear for testing. But the query plans, so I would be carefull in saying one scenario over another is truely best if you compare in a single batch. Now all that said, a huge difference in elapsed time shows a performance change and when I say huge took 30minutes to run first time then 30 seconds with second query.



Post #1276584
Posted Monday, April 2, 2012 7:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 5, 2014 12:02 AM
Points: 315, Visits: 344
Hmm, r u sure? Customer no 6 took product A and no B, customer 7 took product B and no A, both didn't take C, but query doesn't return them.
Regards
Mike
Post #1276596
Posted Monday, April 2, 2012 7:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 5, 2014 12:02 AM
Points: 315, Visits: 344
Jonathan AC Roberts (4/2/2012)
michal.lisinski (4/2/2012)
Imho
SELECT DISTINCT CustomerID
FROM [#Purchase]
WHERE (ProductCode IN ('A','B')) AND (CustomerID NOT IN
(SELECT DISTINCT CustomerID
FROM [#Purchase]
WHERE (ProductCode = 'C')))
GROUP BY CustomerID, ProductCode
HAVING (COUNT(CustomerID) = 1)

Regards
Mike

This also will select customers who have purchased just one of 'A' or 'B' and not purchased 'C'. the requirement was that they should have purchased both 'A' and 'B' and not purchased 'C'.
Post #1276603
Posted Monday, April 2, 2012 7:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 5, 2014 12:02 AM
Points: 315, Visits: 344
Jonathan AC Roberts (4/2/2012)
michal.lisinski (4/2/2012)
Imho
SELECT DISTINCT CustomerID
FROM [#Purchase]
WHERE (ProductCode IN ('A','B')) AND (CustomerID NOT IN
(SELECT DISTINCT CustomerID
FROM [#Purchase]
WHERE (ProductCode = 'C')))
GROUP BY CustomerID, ProductCode
HAVING (COUNT(CustomerID) = 1)

Regards
Mike

This also will select customers who have purchased just one of 'A' or 'B' and not purchased 'C'. the requirement was that they should have purchased both 'A' and 'B' and not purchased 'C'.



Hmm, r u sure? Customer no 6 took product A and no B, customer 7 took product B and no A, both didn't take C, but query doesn't return them.
Regards
Mike
Post #1276605
Posted Monday, April 2, 2012 7:32 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 22, 2014 8:23 AM
Points: 338, Visits: 1,429
michal.lisinski (4/2/2012)
Jonathan AC Roberts (4/2/2012)
michal.lisinski (4/2/2012)
Imho
SELECT DISTINCT CustomerID
FROM [#Purchase]
WHERE (ProductCode IN ('A','B')) AND (CustomerID NOT IN
(SELECT DISTINCT CustomerID
FROM [#Purchase]
WHERE (ProductCode = 'C')))
GROUP BY CustomerID, ProductCode
HAVING (COUNT(CustomerID) = 1)

Regards
Mike

This also will select customers who have purchased just one of 'A' or 'B' and not purchased 'C'. the requirement was that they should have purchased both 'A' and 'B' and not purchased 'C'.



Hmm, r u sure? Customer no 6 took product A and no B, customer 7 took product B and no A, both didn't take C, but query doesn't return them.
Regards
Mike


Hi Mike,
I tried the following on your code:
;WITH [#Purchase] AS
(
SELECT 'A' ProductCode, 1 CustomerId UNION ALL
SELECT 'B' ProductCode, 1 CustomerId UNION ALL
SELECT 'A' ProductCode, 2 CustomerId UNION ALL
SELECT 'B' ProductCode, 3 CustomerId UNION ALL
SELECT 'A' ProductCode, 4 CustomerId UNION ALL
SELECT 'A' ProductCode, 4 CustomerId UNION ALL
SELECT 'A' ProductCode, 5 CustomerId UNION ALL
SELECT 'B' ProductCode, 5 CustomerId UNION ALL
SELECT 'C' ProductCode, 5 CustomerId
)
SELECT DISTINCT CustomerID
FROM [#Purchase]
WHERE (ProductCode IN ('A','B')) AND (CustomerID NOT IN
(SELECT DISTINCT CustomerID
FROM [#Purchase]
WHERE (ProductCode = 'C')))
GROUP BY CustomerID, ProductCode
HAVING (COUNT(CustomerID) = 1)

Customer 1 has bought 'A' and 'B' and not 'C' and he gets found correctly
Customer 2 has bought 'A' and not 'B' and not 'C' and he gets found incorrectly
Customer 3 has bought not 'A' and 'B' and not 'C' and he gets found incorrectly
Customer 4 has bought two 'A' and not 'B' and not 'C' and he doesn't get found correctly
Customer 5 has bought 'A' and 'B' and 'C' and he doesn't get found correctly
Post #1276616
Posted Friday, May 11, 2012 3:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, June 23, 2013 10:39 PM
Points: 231, Visits: 506
select * from
(
select id,max(case when cd = 'A' then 1 else 0 end) + max(case when cd = 'B' then 2 else 0 end) + max(case when cd = 'C' then 4 else 0 end) status from a
--where cd in ('A','B','C')
group by id
) dta
where
1=1
--and status = 3 -- a,b but not c
--and status = 5--does have a,c but not b
--and status = 6--does have b,c but not a
--and status = 7 --all 3
--and status = 0 --none of them
--and status = 1 -- just A
--and status = 2 -- just b
and status = 4 -- just C


Replace Id with CustomerId and cd with ProductCode. You can generalize the code the way you want..You can use analytic function instead of group by as well.


GulliMeel

Finding top n Worst Performing queries
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw
Post #1298425
« Prev Topic | Next Topic »

Add to briefcase «««7891011»»»

Permissions Expand / Collapse