Archives of the TeradataForum

Message Posted: Tue, 21 Mar 2006 @ 19:24:46 GMT

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

Try something like this...

You can use the order OLAP function moving differential (value - previous row's summed value) to find the difference between the current row and the previous row. It works well. The partition column needs to be the partitioning range or scope of the comparisons (not knowing any of your schema I used account number). This limits the differential within each distinct account number.

You need to order the rows by the partition columns and any order of comparison (usually by transaction date or something similar). The first row within a partition will always result in NULL since it cannot be compared to a previous row.

select (case when col5_diff is null or (col5_diff = 0) then 0 else 1 end)
       + (case when col6_diff is null or (col6_diff = 0) then 0 else 2 end)
       + (case when col7_diff is null or (col7_diff = 0) then 0 else 4 end)
       + (case when col8_diff is null or (col8_diff = 0) then 0 else 8 end)
       + (case when col9_diff is null or (col9_diff = 0) then 0 else 16 end)
       + (case when col10_diff is null or (col10_diff = 0) then 0 else 32 end)
       + (case when col11_diff is null or (col11_diff = 0) then 0 else 64 end)
       + (case when col12_diff is null or (col12_diff = 0) then 0 else 128 end)
       + (case when col13_diff is null or (col13_diff = 0) then 0 else 256 end)
       + (case when col14_diff is null or (col14_diff = 0) then 0 else 512 end) as resultnum
from (select col5 - sum(col5) over (partition by acctnum
                                    order by acctnum,transdate
                                    rows between 1 preceding and 1 preceding) as col5_diff
            ,col6 - sum(col6) over (partition by acctnum
                                    order by acctnum,transdate
                                    rows between 1 preceding and 1 preceding) as col6_diff
            ,col7 - sum(col7) over (partition by acctnum
                                    order by acctnum,transdate
                                    rows between 1 preceding and 1 preceding) as col7_diff
            ,col8 - sum(col8) over (partition by acctnum
                                     order by acctnum,transdate
                                     rows between 1 preceding and 1 preceding) as col8_diff
            ,col9 - sum(col9) over (partition by acctnum
                                     order by acctnum,transdate
                                     rows between 1 preceding and 1 preceding) as col9_diff
            ,col10 - sum(col10) over (partition by acctnum
                                     order by acctnum,transdate
                                     rows between 1 preceding and 1 preceding) as col10_diff
            ,col11 - sum(col11) over (partition by acctnum
                                      order by acctnum,transdate
                                      rows between 1 preceding and 1 preceding) as col11_diff
            ,col12 - sum(col12) over (partition by acctnum
                                      order by acctnum,transdate
                                      rows between 1 preceding and 1 preceding) as col12_diff
            ,col13 - sum(col13) over (partition by acctnum
                                      order by acctnum,transdate
                                      rows between 1 preceding and 1 preceding) as col13_diff
            ,col14 - sum(col14) over (partition by acctnum
                                      order by acctnum,transdate
                                      rows between 1 preceding and 1 preceding) as col14_diff
        from tablea) a

Kyle Prescott
UnumProvident, IODS

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