Jeff Moden

Forum Replies Created

Viewing 15 posts - 51,166 through 51,180 (of 59,091 total)

RE: Adding/updating rows of data that is missing but not in sequential order

p.s.
Longer code is not always slower code. Longer code does the ol' "Divide and Conquer". The code I posted is what they call "Set Based" programming and 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 12, 2008 at 11:34 pm

#801855
RE: Adding/updating rows of data that is missing but not in sequential order

As you're finding out, the hard part is to "smear" the existing data into the new rows for the missing years. This type of smearing is VERY procedural 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 12, 2008 at 11:15 pm

#801852
RE: Adding/updating rows of data that is missing but not in sequential order

Matt is correct about the Tally table... but a Tally table isn't really worth a hoot when it comes to performance unless you also build the necessary Clustered Primary Key......
--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:04 pm

#801850
RE: What would be the best way to learn SQL server 2005 BI

JD (4/12/2008)
Do you read or reference one or two tech books evry other month?, do online training? answer posts online? etc etc..its those kinda questions i ask...to get direction...i definetly...
--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 9:27 pm

#801849
RE: complex query

Heh... I've made many such a mistake... thanks for the feedback.
--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 9:12 pm

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

Sometimes, "old school" works better...
SET NOCOUNT ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT IDENTITY(INT,1,1) AS RowNum,
ID,ST,Date
INTO #MyHead
...
--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 4:33 pm

#801835
RE: complex query

So, what was the problem and how did you fix it?
--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 2:29 pm

#801825
RE: What would be the best way to learn SQL server 2005 BI

Like practicing the piano, either you invest the time to get really good... or you don't. There are no shortcuts. For example, I recently answered a post where...
--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 2:03 pm

#801821
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

Viewing 15 posts - 51,166 through 51,180 (of 59,091 total)