Re sequence transactions in database table

Database
Enthusiast

Re sequence transactions in database table

I've got some data in a Teradata database that needs to have its transaction sequence re-sequenced so that the transactions show up like this:

+------+---------+-----+----+-----+
|Tx_Seq|Tx_Date |O/Bal|Qty |C/Bal|
+------+---------+-----+----+-----+
|1 |1/08/2012|0 |500 |500 |
+------+---------+-----+----+-----+
|1 |2/08/2012|500 |-100|400 |
+------+---------+-----+----+-----+
|1 |3/08/2012|400 |250 |650 |
+------+---------+-----+----+-----+
|2 |3/08/2012|650 |100 |750 |
+------+---------+-----+----+-----+
|3 |3/08/2012|750 |-400|350 |
+------+---------+-----+----+-----+
|4 |3/08/2012|350 |-100|250 |
+------+---------+-----+----+-----+
|1 |4/08/2012|250 |200 |450 |
+------+---------+-----+----+-----+
|1 |5/08/2012|450 |100 |550 |
+------+---------+-----+----+-----+

It currently looks like this:

+------+---------+-----+----+-----+
|Tx_Seq|Tx_Date |O/Bal|Qty |C/Bal|
+------+---------+-----+----+-----+
|1 |1/08/2012|0 |500 |500 |
+------+---------+-----+----+-----+
|1 |2/08/2012|500 |-100|400 |
+------+---------+-----+----+-----+
|3 |3/08/2012|400 |250 |650 |
+------+---------+-----+----+-----+
|2 |3/08/2012|650 |100 |750 |
+------+---------+-----+----+-----+
|4 |3/08/2012|750 |-400|350 |
+------+---------+-----+----+-----+
|1 |3/08/2012|350 |-100|250 |
+------+---------+-----+----+-----+
|1 |4/08/2012|250 |200 |450 |
+------+---------+-----+----+-----+
|1 |5/08/2012|450 |100 |550 |
+------+---------+-----+----+-----+

Note that the tx_seq is out of normal ordering for o/Bal for 3/08/2012 . I've tried analytical functions such as ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING as means to re-sequence but could not make it work. Does anyone know how to do this?

Tags (1)