Jeff Moden

Forum Replies Created

Viewing 15 posts - 45,391 through 45,405 (of 59,095 total)

RE: count of consecutive value

Ok... coming full circle back to Martin's original problem...
Here's the test data...
USE TempDb --Just a safe place to run things
GO
--DROP TABLE dbo.YourTable
GO
--===== Populate the test 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)

January 31, 2009 at 3:18 pm

#936032
RE: count of consecutive value

By the way... if anyone else wants to study the code, the original "puzzle" question and two sets of data are available at the following URL...
http://www.sqlmag.com/article/articleid/93462/sql_server_blog_93462.html
... and like I've 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)

January 31, 2009 at 2:12 pm

#936021
RE: count of consecutive value

Nah... I take it back... not clever... freakin' brilliant. The key to this whole thing is the grouping. You've gotta be able to group each "cluster" of rows...
--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)

January 31, 2009 at 1:47 pm

#936020
RE: count of consecutive value

I took a look at the winning solution for that contest... pretty clever.
--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)

January 31, 2009 at 12:53 pm

#936019
RE: count of consecutive value

Jack Corbett (1/31/2009)
You should try both, evaluate execution plans, and performance and choose the one that performs best. In my experience Jeff's solutions usually out-perform others.
The only way 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)

January 31, 2009 at 9:11 am

#935996
RE: count of consecutive value

dubem1 (1/31/2009)
It's for statistics purpose to analyse the tendencies of students.
Thanks for the solution, it's not simple, I will try it.
Yesterday I browse the web to find by myself 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)

January 31, 2009 at 8:59 am

#935995
RE: Multiple Resultset in a single resultset (UNION)

I still would like to know the business reason behind this...
--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)

January 30, 2009 at 9:21 pm

#935946
RE: Are the posted questions getting worse?

Oh, I absolutely agree... when someone said that SSC would have 10-15 million members in 5-10 years, I made the correction that it would have 200 users and 10-15 million...
--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)

January 30, 2009 at 9:18 pm

#935944
RE: Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5

LeeBear35 (1/30/2009)
I have encountered problems with Merry Go Round issues too. On small samples it would always work but as data sets grew it was more and more of 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)

January 30, 2009 at 8:36 pm

#935941
RE: Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5

Martin Stephenson (1/30/2009)
Great article, I 've been able to apply it to a pain in the neck I've been working on, but at the risk of looking like a thicko...
--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)

January 30, 2009 at 8:35 pm

#935940
RE: count of consecutive value

dubem1 (1/30/2009)
Sure
I have a Student table in relation with an exam table. So each student has done n exams.
Each exam has a final cote (the result code in my...
--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)

January 30, 2009 at 7:22 pm

#935935
RE: How do you archive data?

AH! Now that's more like it! Long live "hoopla"! 😛
--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)

January 30, 2009 at 6:53 pm

#935931
RE: How do you archive data?

Thanks for the success story, Gabriel. Like I said, I've not used partitioned tables in production, yet. From all the hoopla in BOL, I have to say I'm...
--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)

January 30, 2009 at 6:33 pm

#935927
RE: Are the posted questions getting worse?

tosscrosby (1/30/2009)
Uh-Oh, Gail's got a light-saber (SP?)..........time for the DARK SIDE to beware.
New haircut, too... although she might try using a little face wax. 😛
--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)

January 30, 2009 at 6:15 pm

#935926
RE: OPENROWSET select statement performance -- runs in 4 seconds on test server vs. 3 minutes on production server.

If someone or something has the file open when your code runs, it will give such an error message. Bouncing the server seems to clear it because the file...
--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)

January 30, 2009 at 5:53 pm

#935919

Viewing 15 posts - 45,391 through 45,405 (of 59,095 total)