Jeff Moden

Forum Replies Created

Viewing 15 posts - 51,136 through 51,150 (of 59,098 total)

RE: total time difference between more than two dates

Heh... you started it... was hoping you had some final code 😉
--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)

April 14, 2008 at 11:42 am

#802205
RE: Slow clustered index seek?

Ooohhh... not good. Just wait till you try a bunch of inserts that are close to each other for the cSpecialty column... it's a 4 letter word... WAIT....
--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)

April 14, 2008 at 11:40 am

#802204
RE: total time difference between more than two dates

Sorry, Gus... we're posting in parallel...
The -1 addition will work but I think the tweek needs to be extended to the days column, as well.
--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)

April 14, 2008 at 11:33 am

#802201
RE: total time difference between more than two dates

GSquared (4/14/2008)
Jeff, good article. Just to add to it, you can do this:
declare @D1 datetime, @D2 datetime
select @d1 = '02/Nov/06 9:14:21 AM', @d2 = '19/Apr/07 11:52:31 AM'
select datepart(year, @d2-@d1)-1900...
--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)

April 14, 2008 at 11:31 am

#802199
RE: Subsitute of TO_CHAR in transact SQL???

Matt Miller (4/14/2008)
Jeff Moden (4/14/2008)
I wonder which would work more quickly? I think the CLR might win this one because it uses implicit functions...
As of right now - "straight...
--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)

April 14, 2008 at 10:51 am

#802178
RE: Oracle LEAD function - Is there an equivalent function in SQL Server ??

antonio.collins (4/14/2008)
LEAD()/LAG() give access to next/prior rows in an ordered set without needing a self join.
http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php
Thanks, Antonio.
Heh... "Ordered Sets" in an RDBMS... I use them a fair bit but what...
--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)

April 14, 2008 at 10:44 am

#802171
RE: Generate CREATE TABLE script from DataTable structure

Temp table???? What temp table????
--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)

April 14, 2008 at 10:35 am

#802166
RE: Variable legnth violation

Greg... stupid question on my part, but have you tried using RTRIM to get rid of the trailing spaces?
--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)

April 14, 2008 at 10:35 am

#802165
RE: Table vs View

TheSQLGuru (4/14/2008)
On a side note, I personally despise views!! As a consultant they REALLY confound my efforts to examine/tune my client's queries!
Hear here! I second that... wasn't going...
--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)

April 14, 2008 at 10:31 am

#802162
RE: Oracle LEAD function - Is there an equivalent function in SQL Server ??

David.Mizelle (4/14/2008)
Sorry guys.... I should have posted the expected result set a long, long time ago. BEFORE I GO ANY FARTHER, THANK YOU THANK YOU THANK YOU FOR 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)

April 14, 2008 at 10:28 am

#802160
RE: String concatenation as a procedure variable

david.buncle (4/14/2008)
TEST? This datatype isn't recognised for SQL Server 2000... Did you mean TEXT?, if so this is invalid for a local variable.
... and, although you can't declare a local...
--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)

April 14, 2008 at 10:24 am

#802158
RE: String concatenation as a procedure variable

david.buncle (4/14/2008)
TEST? This datatype isn't recognised for SQL Server 2000... Did you mean TEXT?, if so this is invalid for a local variable.
Yes... TEXT... coffee deprivation while typing. Thank...
--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)

April 14, 2008 at 10:23 am

#802157
RE: Linking to next row using a join on row_number()+1=row_number()

Derek Dongray (4/14/2008)
Both Jeff's and Peso's queries do not eliminate pairs where the status stays the same.
Sorry... with all the other stuff, I missed that requirement altogether.
Additionally, as Peso...
--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)

April 14, 2008 at 10:22 am

#802155
RE: Subsitute of TO_CHAR in transact SQL???

I wonder which would work more quickly? I think the CLR might win this one because it uses implicit functions...
--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)

April 14, 2008 at 9:54 am

#802145
RE: Trigger: Looping though multiple rows in the deleted table?

GilaMonster (4/14/2008)
The WHERE should be an ON
CREATE TRIGGER On_Delete
ON tblActivityArchive
AFTER DELETE
AS
BEGIN...
--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)

April 14, 2008 at 9:49 am

#802143

Viewing 15 posts - 51,136 through 51,150 (of 59,098 total)