Jeff Moden

Forum Replies Created

Viewing 15 posts - 30,541 through 30,555 (of 59,101 total)

RE: Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle)

codebyo (3/29/2012)
SELECT
CustomerId
FROM Purchase
WHERE ProductCode IN ('A','B', 'C')
GROUP BY CustomerID
having sum(case when ProductCode = 'A' then 1 else 0 end) > 0
and sum(case when ProductCode...
--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.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 29, 2012 at 6:22 pm

#1466679
RE: Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle)

venoym (3/29/2012)
I haven't tested the performance as I don't have a setup to do that,
Heh... sure you do. I mentioned that I attached code to the million row setup...
--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.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 29, 2012 at 6:11 pm

#1466677
RE: Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle)

Wavesailor (3/29/2012)
Thanks for article and comments - I learnt a lot.
In MySQL 😀 this is the fastest by far:
SELECT
CustomerId
FROM Purchase
WHERE ProductCode IN ('A','B', 'C')
GROUP BY CustomerID
having sum(case...
--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.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 29, 2012 at 6:09 pm

#1466675
RE: Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle)

James Dingle-651585 (3/29/2012)
Comparing syntax without an idea of the performance signature is a bit useless IMHO.
I'm 100% in agreement with you. That's why I stated the following in the...
--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.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 29, 2012 at 5:57 pm

#1466670
RE: Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle)

Toby Harman (3/29/2012)
One concern I had was looking at the query plan, the EXCEPT does a clustered index scan. That may be a function of the small data set, but...
--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.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 29, 2012 at 5:46 pm

#1466663
RE: Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle)

Arjun S (3/28/2012)
Another way to get the result would be to use Intersect and then combine it with Except. The distinct part is handled implicitly.
--===== Find Customers that bought both...
--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.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 29, 2012 at 5:33 pm

#1466660
RE: Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle)

mark hutchinson (3/28/2012)
@jeff
Did you compare the speed of this query against one where your first (Group By) From clause is a Select Distinct (sub) query?
Nice article. It builds nicely...
--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.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 29, 2012 at 5:15 pm

#1466655
RE: Whose job is it?

Just my 2 cents and to reiterate some of what has already been said. Yes, they should tell you... but you shouldn't count on it. It should 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.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 29, 2012 at 3:51 pm

#1466637
RE: Add varchar columns with colon in between to show time

Are you all set then or do you still need some help to pull all of this off?
--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.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 29, 2012 at 7:35 am

#1466341
RE: Add varchar columns with colon in between to show time

dwain.c (3/26/2012)
Perhaps I didn't adapt your code properly to my temporary table. Can you tell me what I did wrong?
Sorry. Lost track of this post in all the...
--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.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 29, 2012 at 6:49 am

#1466320
RE: Add varchar columns with colon in between to show time

Eugene Elutin (3/29/2012)
Just looking into this thread again...
What I can say, crap data structure design quickly leads to crap and stupid coding you need to do to achieve something good!...
--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.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 29, 2012 at 6:46 am

#1466315
RE: Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle)

Hi folks,
Thanks for the great discussion going on and the thoughtful feedback. I'd love to jump in this instant but I'm on my way to work. I'll 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.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 29, 2012 at 6:34 am

#1466307
RE: how to transpose one colomn to many columns ?

pdiagne83 (3/27/2012)
THANK you
Can I get the result with PIVOT ?
Just double checking... you want the result in separate columns instead of concatenated into one column, correct?.
--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.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 29, 2012 at 6:22 am

#1466300
RE: A Developer's scalar UDF is takeing this query from 3 seconds to over 5 minutes to run

p.s. Almost forgot. Don't forget to add the month and year to your SELECT list if you want to actually show groupings by month and year. After...
--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.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 28, 2012 at 8:23 pm

#1466139
RE: A Developer's scalar UDF is takeing this query from 3 seconds to over 5 minutes to run

MyDoggieJessie (3/27/2012)
Hey Jeff, thanks for that (really)...just not certain how I would work your splitter into what I already have posted a couple posts above...
With using the CTE's that GSquared...
--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.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 28, 2012 at 8:06 pm

#1466137

Viewing 15 posts - 30,541 through 30,555 (of 59,101 total)