Jeff Moden

Forum Replies Created

Viewing 15 posts - 35,941 through 35,955 (of 59,102 total)

RE: cross apply

kevin4u06 (11/24/2010)
This is the table and sample data (Attached below)
my out put shoud be something like this.
My function does the splitting of the address field .
Thank you in advance
A .JPG...
--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)

November 25, 2010 at 1:12 am

#1255467
RE: how many variables can I have?

ColdCoffee (11/22/2010)
make use of some [font="Arial Black"]SET-based code to loop [/font]
Whiskey Tango Foxtrot, CC?????? 😉
--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)

November 25, 2010 at 12:44 am

#1255459
RE: temp table usage

shaun.stuart (11/24/2010)
Temp tables are created and stored in the temdb system database, so look for bottlenecks there. Is tempdb on its own drive and not on the same drive as...
--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)

November 25, 2010 at 12:41 am

#1255458
RE: Converting Quartz.Net time columns to datetime

This will do it...
DECLARE @Ticks BIGINT
SELECT @Ticks = 634262130000000000
SELECT Converted = CAST(@Ticks/864000000000.0 - 693595.0 AS DATETIME)
--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)

November 25, 2010 at 12:18 am

#1255455
RE: Converting Rows into Columns

Craig Farrell (11/23/2010)
For a fast explaination, a fast answer. You're looking for the PIVOT command. Quite handy.
If you'd like more tested code, please see the first link in...
--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)

November 24, 2010 at 11:53 pm

#1255450
RE: Are the posted questions getting worse?

It's a little out of character for me but I know you'll recognize the rhetoric. I just couldn't resist... it was too perfect. Enjoy... 😛
http://www.sqlservercentral.com/Forums/Topic1025567-392-1.aspx#bm1026289
--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)

November 24, 2010 at 8:10 pm

#1255422
RE: Need help building a query.

{edit} wrong 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)

November 24, 2010 at 8:08 pm

#1255420
RE: Are the posted questions getting worse?

LutzM (11/24/2010)
To all of you folks in the U.S.:
May the turkey be tender and juicy and the people you're celebrating with be the ones you love and care about.
My very...
--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)

November 24, 2010 at 8:01 pm

#1255418
RE: Remove duplicate records based on record id, annoying GROUP BY statement

petervdkerk (11/24/2010)
I want to remove duplicates from the DB. here's my code for that:
DECLARE @Count int
DECLARE @companyname nvarchar(50)
DECLARE @id int
DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR
SELECT companyname, Count(*) - 1
FROM prospects
GROUP BY...
--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)

November 24, 2010 at 7:46 pm

#1255416
RE: Need help building a query.

CELKO (11/24/2010)
Can I re-write your DDL to ISO-11179 rules, add keys. I hope you know why NUMERIC(s,p) is not like DECIMAL(s,p). T-SQL has a DATE data type now and...
--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)

November 24, 2010 at 7:14 pm

#1255414
RE: Need help building a query.

Remember the old ways before CROSS APPLY and ROW_NUMBER?
In days of old,
When Knights were bold,
And Windowing was not invented,
He wrapped his code
In a familiar mode
And RBAR was prevented. 😀
To wit...
CREATE...
--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)

November 24, 2010 at 7:01 pm

#1255413
RE: Are the posted questions getting worse?

WayneS (11/24/2010)
Craig Farrell (11/24/2010)
CirquedeSQLeil (11/24/2010)
jcrawf02 (11/24/2010)
Should we point out that his view solution doesn't *just* give you the duplicates? 😉
Yes - fire away!!!!!!!!!
I just ended up having to thump Joe...
--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)

November 24, 2010 at 5:12 pm

#1255405
RE: Need help building a query.

Ram2002 (11/24/2010)
Thanx Criag. I appreciate all you ppl in this post time.
This was the first post so went wrong. Next time will be in appropriate forum.
Except for it being...
--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)

November 24, 2010 at 4:17 pm

#1255386
RE: Remove duplicate records based on record id, annoying GROUP BY statement

CELKO (11/24/2010)
>> I want to remove duplicates from the DB. here's my code for that: <<
Your code is terrible and using a cursor is really bad SQL programming. What you...
--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)

November 24, 2010 at 3:11 pm

#1255357
RE: Big table 50MB containing just 1000 records ( a few small fields each record)

Iulian -207023 (11/24/2010)
Hi Jeff, it was about 50MB - not sure exactly.
Now the size is 0.28MB - which is great.
I dropped one unclustered index and left only 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)

November 24, 2010 at 2:45 pm

#1255350

Viewing 15 posts - 35,941 through 35,955 (of 59,102 total)