Jeff Moden

Forum Replies Created

Viewing 15 posts - 30,811 through 30,825 (of 59,101 total)

RE: Need help to query a non-normalized database

ckelly 43038 (3/8/2012)
Hi!
I need to create a report regarding the victims of crimes.
I've joined all the tables I need.
But, now I'm stumped and can't proceed.
This is a...
--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 8, 2012 at 8:26 pm

#1456934
RE: Need help to query a non-normalized database

pietlinden (3/8/2012)
Just for fun (and a little learning), I made it work with your example:
SELECT lw.SomeCode, lw.Seq, lw.LWType, lw.Age,lw.Race,lw.Sex,
SUBSTRING(','+lw.Crimes+',',N+1,CHARINDEX(',',','+lw.Crimes+',',N+1)-N-1) AS Value
FROM SCRIDB.dbo.Tally t
CROSS JOIN lwNames lw
WHERE N <...
--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 8, 2012 at 8:24 pm

#1456933
RE: Evils of coalesce on a column in the Where clause due to nullable parameters

MadTester (3/8/2012)
So my top tip is, look at procs first, rewrite if necessary, then look at indexing.
I absolutely agree. "Performance is in the code."
Very well done and thanks for...
--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 8, 2012 at 8:15 pm

#1456932
RE: Flat File Connection Mgr / EOL

BSavoie (3/8/2012)
I'm not sure why these sorts of files would have CR/LF's in the first place
Actually, it's a form of "grouping" that can help you ensure that you don't have...
--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 8, 2012 at 8:04 pm

#1456930
RE: How to convert some columns into rows with the first two columns still retained as columns

At the very least, you'd have to list all of the column names in the UNPIVOT.
I believe I know a way to do this fairly easily. If you'd post...
--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 8, 2012 at 7:52 pm

#1456927
RE: Load Positional File

BSavoie (3/8/2012)
Performance is acceptable now.
So what is it? What kind of performance did you end up with?
--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 8, 2012 at 7:34 pm

#1456925
RE: Work out which column value is repeated the most

sku370870 (3/2/2012)
I need to choose one, I have no reason to choose one over the other
I'll never understand such scenarios. Glad you're all set.
--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 8, 2012 at 7:19 pm

#1456924
RE: Week Starts On Saturday?

Any additional thoughts on this, Terry?
--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 8, 2012 at 7:09 pm

#1456920
RE: Getting two different rows as columns

Eugene Elutin (3/1/2012)
dwain.c (3/1/2012)
My comment was that it didn't work after I exploded the record set out to 50000 records with my CROSS APPLY INSERTs.
You may try it yourself if...
--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 8, 2012 at 6:56 pm

#1456918
RE: Erratic SP Performance

Dave Mason (3/7/2012)
I ended up opening a support ticket with Microsoft. During initial correspondence with the MS rep, it came to light that SP1 was not installed on 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 8, 2012 at 6:46 pm

#1456914
RE: Column Name Restrictions / Description Keyword

jcb (3/6/2012)
Create a pattern, if your team mates can read it and understand it easy its a good pattern.
Returning two homonimal columns in a query is a nuisance, I agree...
--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 8, 2012 at 6:25 pm

#1456910
RE: 30-day demo period

phung.quang (3/2/2012)
Hi, thank you for sharing
SPAM reported.
--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 8, 2012 at 6:03 pm

#1456904
RE: Are the posted questions getting worse?

L' Eomot Inversé (3/8/2012)
Jeff Moden (3/7/2012)
Koen Verbeeck (3/7/2012)
[/url]
(if SOPA got through, this site could be taken down)
Not without due process of proper notification and the opportunity to comply. 😉
That's not...
--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 8, 2012 at 5:59 pm

#1456903
RE: Are the posted questions getting worse?

Stefan Krzywicki (3/7/2012)
Jeff Moden (3/7/2012)
Koen Verbeeck (3/7/2012)
[/url]
(if SOPA got through, this site could be taken down)
Not without due process of proper notification and the opportunity to comply. 😉
Not necessarily. All...
--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 8, 2012 at 5:49 pm

#1456897
RE: Incrementing a table name

So, did the SMALLDATETIME realization and the fact that the last 30 seconds of the day are rounded up to the next day shed any light on your partitioning problem...
--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 8, 2012 at 8:10 am

#1456551

Viewing 15 posts - 30,811 through 30,825 (of 59,101 total)