Archives of the TeradataForum
Message Posted: Tue, 05 Mar 2013 @ 17:36:06 GMT
<-- Anonymously Posted: Tuesday, March 05, 2013 12:34 -->
can anyone advise on a different way to find the middle value in a table holding monthly reporting dates?
If I have these months (dates are all YYYY-MM-DD )
TEST_DATA Month_Dt Amount 2012-01-01 100 2012-02-01 300 2012-03-01 400 2012-04-01 400
I need the middle value in the past three months. It can be said to be the median value over the past three months.
No data is treated as NULL.
The answer would be
Month_Dt Amount 2012-01-01 Null (middle of NULL, NULL, 100) 2012-02-01 100 (middle of NULL, 100, 300) 2012-03-01 300 (middle of 100,300,400) 2012-04-01 400 (middle of 300,400,400)
I hope that's clear.
My approach has been
1. to build a list of the Month_dt plus its related prior months,
REPORT_DATES Report_Dt Month_Dt 2012-01-01 2011-11-01 2012-01-01 2011-12-01 2012-01-01 2012-01-01 2012-02-01 2011-12-01 2012-02-01 2012-01-01 2012-02-01 2012-02-01 2012-03-01 2012-01-01 etc
So Report_dt 2012-02-01 would look for Amounts in 2011-12-01, 2012-01-01 , 2012-02-01
2. Use this list to left join to the source table, partition the row_number by the Report_Dt, order by the Amount , take the second ordered value.
SELECT T1.Report_Dt ,T2.Amount FROM REPORT_DATES T1 LEFT JOIN TEST_DATA T2 ON T1.Month_Dt = t2.Month_dt QUALIFY ROW_NUMBER() OVER(PARTITION BY T1.Report_dt ORDER BY T2.Amount) = 2 ORDER BY 1 ;
Apart from putting the list into a derived table is there a way to do this with other SQL techniques in a single scan?
I've tried other methods using Window functions and RESET over preceding rows but cannot get it give the same answer.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|