Jeff Moden

Forum Replies Created

Viewing 15 posts - 25,936 through 25,950 (of 59,102 total)

RE: Need help in building a SQL query?

Just to backup Maggo's solution... the method he used is called a "CROSSTAB". You could also use a PIVOT although the CROSSTAB solution is usually a touch faster. ...
--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)

September 16, 2013 at 6:52 pm

#1650662
RE: Extract string between to characters

Trestire (9/13/2013)
First of all thanks for your time.
I'll paraphrase my post:
I've a string in following format. I've this string in my a table column:
<1|458|test|q><2|789|test2|s><5|567|test3|p>
First I've to extract the strings between...
--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)

September 16, 2013 at 6:37 pm

#1650660
RE: Rounding a number up to the nearest 5

smrobin (9/16/2013)
Thanks for the replies guy! I used your combined logic and got it to work! Thanks again - love this site!
Two way street here, my friend. ...
--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)

September 16, 2013 at 6:06 pm

#1650651
RE: Concat column values

Sean Lange (9/16/2013)
Jeff Moden (9/15/2013)
dwain.c (9/15/2013)
Jeff Moden (9/15/2013)
With the idea of "teaching a man to fish" in full swing, please see the following article.
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
For future posts, please see the article...
--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)

September 16, 2013 at 6:00 pm

#1650650
RE: Second Highest

siggemannen (9/16/2013)
SELECT MAX(salary) FROM t
WHERE salary < (SELECT MAX(salary) FROM t)
Apologies. I missed this. This would do it while meeting all of the expressed and implied restrictions.
--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)

September 16, 2013 at 5:57 pm

#1650649
RE: Second Highest

psingla (9/16/2013)
Jeff Moden (9/16/2013)
psingla (9/16/2013)
hopefully following will help
http://pramodsingla.wordpress.com/category/interview-questions-2/
siggemannen (9/16/2013)
DENSE_RANK should do it 😉
Or SELECT 'me' if you're a bit on the joking side...
Or:
SELECT MAX(salary)
FROM t
WHERE salary < (SELECT MAX(salary) FROM...
--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)

September 16, 2013 at 5:48 pm

#1650647
RE: Second Highest

psingla (9/16/2013)
hopefully following will help
http://pramodsingla.wordpress.com/category/interview-questions-2/
siggemannen (9/16/2013)
DENSE_RANK should do it 😉
Neither of the proposed solutions meet the restrictions posed by the OP.
--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)

September 16, 2013 at 7:48 am

#1650446
RE: Second Highest

karunakar2351 (9/16/2013)
Could anyone know how to write the query fro fetching the second highest salary without using top, rowcount and rank keywords?
Why the strange restrictions?
--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)

September 16, 2013 at 7:46 am

#1650443
RE: Are the posted questions getting worse?

Greg Edwards-268690 (9/16/2013)
Hope those in Colorado are doing OK.
Floods are so devastating.
Especially when in areas you'd never expect them, and not related to spring run off.
My prayers are with you.
Ditto...
--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)

September 16, 2013 at 7:21 am

#1650430
RE: Cannot figure out my update correctly

Oracle765 (9/15/2013)
ok thanks for the update, the query works but yes I agree, i will speak to the powers that be, thanks again all
You could also make a persisted calculated...
--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)

September 15, 2013 at 10:58 pm

#1650316
RE: Issues with msdb.dbo.sp_send_dbmail procedure

What are you calling "whitespace"? Precisely what is in the variable when you get the error? Is it a NULL?
--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)

September 15, 2013 at 8:42 pm

#1650308
RE: Concat column values

dwain.c (9/15/2013)
Jeff Moden (9/15/2013)
With the idea of "teaching a man to fish" in full swing, please see the following article.
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
For future posts, please see the article at the first link...
--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)

September 15, 2013 at 8:35 pm

#1650307
RE: Extract string between to characters

Trestire (9/13/2013)
I've a string in following format:
<1|458|test|q><2|789|test2|s><5|567|test3|p>
First I've to extract the strings between < and >
like: 1|458|test|q 2|789|test2|s 5|567|test3|p
after that I've to split the the pipes: 1 458...
--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)

September 15, 2013 at 8:30 pm

#1650306
RE: Birthdate

Erland Sommarskog (9/15/2013)
Jeff Moden (9/15/2013)
Doesn't work, Erland. Consider a baby born on 12/31 of some year and using your code the next day.
In that case, there is some other...
--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)

September 15, 2013 at 5:16 pm

#1650297
RE: Birthdate

Erland Sommarskog (9/14/2013)
datediff(YEAR, BirthDate, getdate())
Doesn't work, Erland. Consider a baby born on 12/31 of some year and using your code the next day.
--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)

September 15, 2013 at 1:47 pm

#1650289

Viewing 15 posts - 25,936 through 25,950 (of 59,102 total)