Archives of the TeradataForum

Message Posted: Tue, 05 Mar 2013 @ 17:36:06 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Tuesday, March 05, 2013 12:34 -->

Hi,

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.

regards

	<Prev	Next>		<<First	<Prev	Next>	Last>>