Median Calculations
-
Heh... yeah, I've been burned by the throw-away timeout on this forum many a time at first... I've learned to always [Ctrl-a][Ctrl-c] before I even think of hitting [Post Reply]... has really saved a lot of retyping...
--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) -
I've been burned as well on the timeout issue and it seems that the times I forget the old [Ctrl-a][Ctrl-c] is when I get burned.
Murphy's law I guess.> have a table with just the mile_codes in it and tried tying that in but can't seem to get it to work.
Do an outer join to that table...
Jeff,
The first thing I tried was the Outer join but it didn't work. Maybe I'm missing something simple, I don't know.
Here is what I did in the final SELECT
SELECT DISTINCT
m.Rep,
m.Mile_Code,
(
(SELECT TOP 1 DeltaT FROM
(SELECT TOP 50 PERCENT DeltaT FROM #Steps t
WHERE t.Rep = m.Rep AND t.Mile_Code = m.Mile_Code
ORDER BY DeltaT ASC
) lo
ORDER BY DeltaT DESC)
+(SELECT TOP 1 DeltaT FROM
(SELECT TOP 50 PERCENT DeltaT FROM #Steps t
WHERE t.Rep = m.Rep AND t.Mile_Code = m.Mile_Code
ORDER BY DeltaT DESC
) hi
ORDER BY DeltaT ASC)
) / 2 AS MEDIAN,
gl_a.seq
FROM #Steps m FULL OUTER JOIN gl_AIDINC gl_a ON m.mile_code = gl_a.description
ORDER BY m.Rep, gl_a.seq
-
Oooo, ... you've implimented this as correlated subqueries in the SELECT list... not what I had in mind at all... I gotta think about this a bit but I was counting on you having ALL the medians available as a derived table in the FROM clause.
--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) -
Hey folks... I had reason to revisit this post to demo to someone else... I don't know what change was recently made to the 2k server I work on but where I said...
He does demo a way to do the same thing in SQL Server 7/2000 in his "Listing 3" ( http://www.sqlmag.com/Files/09/49872/Listing_03.txt ) , but, as he points out, it's horribly slow (that makes SQL Server 2005 better, right? NOT!). The reason is, he made the mistake of using aggragates for this instead of continuing the sort for a single row "page" as if doing paging in a GUI.
... doesn't appear to be true any longer... the aggragate method works just fine... on my box, anyway.
--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)
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply