Viewing 15 posts - 10,171 through 10,185 (of 14,953 total)
jsanborn (4/2/2009)
I'm afraid the coalesce won't work either. It's essentially doing the same thing as the case statement. Comparing values from adjacent columns will not produce a correct result.
On every...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 12:33 pm
You're going to have to make sure that whatever you're concatenating it with is the same colation. You can use Convert for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 12:30 pm
You can kind of do that with Case statements.
The big question is, what do you want to do with ties? What if A = B?
Here's a sample with Case...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 12:28 pm
I would do the whole thing this way:
declare @Total float;
select @Total =
(select count(*)
from #Person);
select
'0-10' as Age,
count(*) as Total,
count(*)/@Total as Percentage
from #Person
where age between 0 and 10
union...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 12:00 pm
The problem with flash storage is that it wears out rapidly when under constant rewrites. Rapidly compared to hard drives, that is. So, while it's really good for...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 11:41 am
"With" is how you start a CTE, so yes, you would. Since it has to have a semicolon to end the command before it, I've gotten myself into the...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 9:57 am
I'll go with they have their place in admin scripts and a few things like that. Timothy's stuff sounds about right.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 9:51 am
mike brockington (4/2/2009)
Jack Corbett (4/2/2009)
Yeah, but it was published on April 1st and in the April 1st newsletter.
Yeah, but here in the UK, the April 1st newsletter doesn't arrive until...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 9:31 am
That's a standard "adjacency hierarchy". You can resolve it with a recursive CTE. Look up Common Table Expressions in Books Online, and it will have an example of...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 9:28 am
This is the kind that gives me hope:
http://www.sqlservercentral.com/Forums/Topic688912-149-1.aspx
This is the kind that drives me to The Thread:
http://www.sqlservercentral.com/Forums/Topic688302-338-1.aspx
I keep going on the second type because of the first type.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 9:25 am
--- Create sample data
if object_id(N'tempdb..#T') is not null
drop table #T;
if object_id(N'tempdb..#T1') is not null
drop table #T1;
--
create table #T ( -- Table2
ID int identity primary key,
Store int,
Audit...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 9:20 am
Gotta go with Jack and Gail on this one. How would you feel if you had to carry around an iPod with recordings of every social gaffe you've ever...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 9:09 am
So long as later columns always have either null or higher values than earlier columns, something like this will work:
if object_id(N'tempdb..#T') is not null
drop table #T;
create table #T (
ID int...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 9:03 am
Add a column alias after the sub-query. Would go after the "'varchar(MAX)') ,1,2,'')" part, between that and the comment. That'll give you a column name you can use...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 8:55 am
If the Value date is in the future, it will end up with a Null value for that sub-query, because of the IsNull(..., getdate()), that I used in the sub-query...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 8:50 am
Viewing 15 posts - 10,171 through 10,185 (of 14,953 total)