Jeff Moden

Forum Replies Created

Viewing 15 posts - 51,181 through 51,195 (of 59,098 total)

RE: total time difference between more than two dates

DateDiff and DateAdd have the tendency to round to whatever the "date part" is and, despite what many folks think, shouldn't be used to calculate what the difference between full...
--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 12, 2008 at 12:58 pm

#801815
RE: Reclaiming space from large TEXT data

Ok... thanks Tim.
--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 12, 2008 at 10:43 am

#801811
RE: How to Validate Bank Routing Using Check Digit

You made the mistake of using ISNUMERIC to check for digits. ISNUMERIC does not equal ISALLDIGITS... try this and see what I mean...
SELECT dbo.check_digit('072---326')
Rather than just have it return...
--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 11, 2008 at 11:26 pm

#801776
RE: How to Calculate Bank Routing Check Digit

You can shrink this down quite a bit...
[font="Courier New"] CREATE FUNCTION CreateABACheckDigit
--===== Created by Jeff Moden
        (@RoutingNumber CHAR(8))
RETURNS INT
     AS
  BEGIN
RETURN (SELECT NULLIF(10-( SUBSTRING(@RoutingNumber,1,1)*3
                          + SUBSTRING(@RoutingNumber,2,1)*7
                          + SUBSTRING(@RoutingNumber,3,1)*1
                          + SUBSTRING(@RoutingNumber,4,1)*3
                          + SUBSTRING(@RoutingNumber,5,1)*7
                          + SUBSTRING(@RoutingNumber,6,1)*1
                          + SUBSTRING(@RoutingNumber,7,1)*3
                          + SUBSTRING(@RoutingNumber,8,1)*7)%10
                      ,10))
    END
[/font]
--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 11, 2008 at 11:04 pm

#801774
RE: Dynamically building a sql Where clause

b.carlson (4/11/2008)
The solution was very simple with an array and a couple of functions.
Thanks for taking the time to respond.
You could thank us by posting the code you used... 😉
--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 11, 2008 at 7:31 pm

#801762
RE: Return row values as columns

Read the article at the URL in my signature line... you'll get a lot better help...
--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 11, 2008 at 7:07 pm

#801758
RE: Dynamically building a sql Where clause

This should really be done in a stored procedure instead of dynamic SQL in a GUI... dynamic SQL in a GUI is a primary source of SQL Injection attacks... 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)

April 11, 2008 at 6:56 pm

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

dmizelle (4/7/2008)
All of it looks good, but I'm lost within the following code, specifically the line starting with "with leader...". I've been working w/SQL for 12+ years and I've...
--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 11, 2008 at 6:53 pm

#801755
RE: Reclaiming space from large TEXT data

Hmmmm... what kind of index did you put on the TEXT column?
[font="Courier New"]CREATE TABLE EraseMe (ID INT, WTD TEXT)
CREATE INDEX Dodah ON EraseMe (WTD)
Server: Msg 1919, Level 16, State 1,...
--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 11, 2008 at 6:30 pm

#801753
RE: want only date portion from datetime datatype in UPDATE statement

Still not going to help on a DATETIME column... A DATETIME column uses only the default system format when displaying data. If you want something else (should be done...
--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 10, 2008 at 6:57 pm

#801244
RE: which one is faster?

I'd rather have more memory so more data can be cached. Also, no matter how fast the processor is, bad code (usually some form of RBAR) is the great...
--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 10, 2008 at 6:48 pm

#801243
RE: SUM Keeps decreasing when it should stay the same:

alorenzini (4/10/2008)
What would be the best way to get you some sample data?
See the UFL in my signature line for some help on that...
--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 10, 2008 at 6:33 pm

#801241
RE: group by ntext?

Ah... thanks for the explanation, Gail... I just figured it out... the reason they need to do the GROUP BY on a comments column is because they're writing an aggregate...
--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 10, 2008 at 6:58 am

#800942
RE: Interview guidelines

GilaMonster (4/10/2008)
At my current place we start with the technical interview. Since we're curently looking for people to do performance tuning and monitoring, I'll usually start with a very simple...
--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 10, 2008 at 6:49 am

#800938
RE: How to split cells in table format?

antonio.collins (4/9/2008)
A matrix report will do the pivoting you describe.
Nicely done...
--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 10, 2008 at 6:07 am

#800908

Viewing 15 posts - 51,181 through 51,195 (of 59,098 total)