Archives of the TeradataForum
Message Posted: Wed, 06 Mar 2013 @ 08:24:44 GMT
Subj: | | Re: 'Median' value of three dates |
|
From: | | Dieter Noeth |
Not really, you should have used some non-ascending values for your example data :-)
If you actually need the median of *three* values there's a simple solution, the median is the sum minus the min and the max:
CASE
ROW_NUMBER() OVER (ORDER BY Month_Dt)
WHEN 1 THEN NULL
WHEN 2 THEN SUM(Amount) OVER (ORDER BY Month_Dt ROWS 2 PRECEDING)
-MAX(Amount) OVER (ORDER BY Month_Dt ROWS 2 PRECEDING)
ELSE SUM(Amount) OVER (ORDER BY Month_Dt ROWS 2 PRECEDING)
-MAX(Amount) OVER (ORDER BY Month_Dt ROWS 2 PRECEDING)
-MIN(Amount) OVER (ORDER BY Month_Dt ROWS 2 PRECEDING) END
Otherwise you probably have to keep your solution
Dieter
|