Jeff Moden

Forum Replies Created

Viewing 15 posts - 41,176 through 41,190 (of 59,069 total)

RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Hugo Kornelis (11/10/2009)
The code I posted was not intended to be proof of breaking the quirky update. This particular comment was only posted to show that the presence of 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)

November 10, 2009 at 5:29 pm

#1077601
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Alexander Kuznetsov-291390 (11/10/2009)
Looks like I should write up another blog post demonstrating how to add multiple rows at a time...
That would be very cool especially if it used neither RBAR...
--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 10, 2009 at 4:02 pm

#1077584
RE: Using OPENROWSET to import CSV files

Jeff Moden (11/6/2009)
Simon Parry (11/6/2009)
Hi
i have tried using BULK INSERT
using
set @string ='BULK INSERT CSVTest FROM ''\\10.1.2.107\rp_uploaded_files\'+@file+''' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = '''')'
exec (@string)
but this requires the destintation table to...
--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 10, 2009 at 3:53 pm

#1077581
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

You have to observe all the rules at once, Hugo. I said in the rules that you can't use a JOIN in conjunction with the "Quirky Update" and an...
--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 10, 2009 at 3:49 pm

#1077579
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Alexander Kuznetsov-291390 (11/10/2009)
Jeff,
In my experience, if we need running totals, then we have some unique order of rows to calculate those totals with respect to. Because we know the running...
--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 10, 2009 at 3:02 pm

#1077556
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Hugo Kornelis (11/10/2009)
... and shaved off another half a mintue - 3:58.
Heh... very cool optimization of the cursor, Hugo. You've managed to upgrade it to only being 33...
--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 10, 2009 at 2:59 pm

#1077554
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Alexander Kuznetsov-291390 (11/10/2009)
Jeff, I am not sure how RBAR is relevant to denormalization, yet when the running totals are stored right in your row, you don't need any joins, any...
--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 10, 2009 at 1:31 pm

#1077484
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

talltop-969015 (11/10/2009)
Alexander Kuznetsov-291390 (11/10/2009)
I decided that I don't want to put it into production - they are just a little bit too complex and they do not look robust to...
--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 10, 2009 at 9:33 am

#1077356
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Alexander Kuznetsov-291390 (11/10/2009)
These are incredibly smart techniques, but after playing with such approaches I decided that I don't want to put it into production - they are just a little...
--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 10, 2009 at 9:22 am

#1077345
RE: Date conversion into AM/PM Format

suresh.theyyath (11/10/2009)
Hope this will works for you!
http://blog.sqlauthority.com/2008/08/14/sql-server-get-date-time-in-any-format-udf-user-defined-functions/
Heh... I wrote such a function earlier in my SQL career... it's a fun function to write because it creates a huge understanding of...
--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 10, 2009 at 7:23 am

#1077234
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Hugo Kornelis (11/10/2009)
Hi Jeff,
Thanks for doing a rewrite of this article.
Just a few comments before I start really reading the article:
1) Being one of the people who tried to grill...
--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 10, 2009 at 6:54 am

#1077200
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

ta.bu.shi.da.yu (11/10/2009)
That's an awesome article Jeff!
Now if only I could work out how to run a query that traverses a directed graph with cycles - but where there is...
--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 10, 2009 at 6:46 am

#1077192
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

dm_unseen-571460 (11/10/2009)
I'm missig the CTE/Recursive approaches to solving these issues. While usually not single pass the beat triangular joins
For Running total see: http://shahamishm.blogspot.com/2009/04/how-to-do-running-sum-using-cte-sql.html
For finding previous row ordered by key_nr 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 10, 2009 at 6:43 am

#1077188
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

ben.mcintyre (11/10/2009)
Superlative. As usual, you make a highly complex and conditional task look simple and straightforward by precisely covering key points. I am now not afraid to try using...
--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 10, 2009 at 6:37 am

#1077184
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

peter-757102 (11/10/2009)
One word Jeff....CLASS
Thank you for the very clear article and all the testing done to make sure that what you claim is 100% correct. But I wouldn't be me...
--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 10, 2009 at 6:33 am

#1077182

Viewing 15 posts - 41,176 through 41,190 (of 59,069 total)