I have a write a procedure to parse a file to identify valid transaction rows,
for ex: Input file has 9 columns as shown below along with sample data ( - indicates any value)
I have loaded this file into a staging table. Now I have to write a procedure to do as follows based on SeqNo and Loan Number
SeqNo = 1 identifies new Loan Number and starting balance for the loan(444444)
SeqNo = 2 & 3 0r 2, 3 & 4 are transaction details for the above loan. There can be more than one transaction which are appeared as separate rows identified by seq no 2,3 or 2,3 & 4.
For ex: To start with SeqNo = 1, Loan Number = 4444444 and balance = 123.
Sp should combine above details with details from seq no 2, 3 into separate columns but in a single row.
If we have 2,3,4 then Balance 123 will get replaced with 666 and will be carried forward until we find another sqe no = 1 which identifies new loan number and its respective balance.
Same process is followed for all the rows.
Please let me know the logic on how and where to start coding this procedure.
Any help is this is highly appreciated.