creating seq Nbr for detail recrods for each header record.

[DFW1999]

I have one stage table with following entries:
ACCT_ID TimeStamp Dflt_Flag
123 10:30:30 Y
123 10:30:31 N
123 10:30:32 N
124 10:30:33 Y
124 10:30:34 N
124 10:30:35 N
124 10:30:36 N
125 10:30:37 Y

All the entries with Default_flag "Y" is kind of header record and I want them to be in the header table and each subsequent entries with default flag "N" along with the default "Y" I want them in the detail(child) table .

In teradata sql(BTEQ) is there any approch which can create two tables out of one stage table with the following info:

Header table (
Acct_id
Timestamp
Dflt_Flg
)
Acct_id TimeStamp Dflt_Flag
123 10:30:30 Y
124 10:30:33 Y
125 10:30:37 Y

Detail table (
Acct_id
HDR_TS
DTL_TS
Dflt_Flag
Seq_Nbr
)
Acct_id HDR_TS DTL_TS Dflt_Flag Seq_Nbr
123 10:30:30 10:30:30 Y 1
123 10:30:30 10:30:31 N 2
123 10:30:30 10:30:32 N 3

124 10:30:33 10:30:33 Y 1
124 10:30:33 10:30:34 N 2
124 10:30:33 10:30:35 N 3
124 10:30:33 10:30:36 N 4

125 10:30:37 10:30:37 Y 1

Finally in Detail table acct_id, HDR_TS, DTL_TS and SeqNbr would be unique to fetch all the detail records for a given record from the Header record.

Any clue would be appreciated.

DFW1999

 

[dnoeth]

Hi DFW1999,
see the answer posted to

http://www.teradata.com/teradataForum/shwmessage.aspx?ForumID=1&MessageID=2936

Dieter