I have source data that looks like this: CustID, Description, Phone_No.
The target data structure looks like this (actual columns): CustID, phone, phonetitle, workphone, workphonetitle, mobilephone, mobilephonetitle, fax, faxtitle.
Sample data:
Code:
CustID Description Phone_No
1 Cellular 1234567890
1 Work 1234560987
1 Spouse 1236521489
2 Spouse 1236549870
3 Home 1236548521
3 Work 1236549514
3 Cellular 1239547892
3 Fax 1236547264
In the example data I can obviously map the following descriptions and phone numbers to the target structure:
The Cellular number would go into the target.mobilephone field and the text "Cellular" can go into the mobilephonetitle field. Likewise source.work can go to the target.workphone fields, source.fax can go into the target.fax fields, and the source.home data can go into the target.phone fields.
The part I am unsure about is how to handle the source.Spouse fields. I want those to go into any of the other phone fields that does not contain data already. So for source.CustID 1 above I would put the Spouse phone number in the target.phone field and the text "Spouse" in the phonetitle field since there is not a mobile number for custid 1. In other records the Spouse phone number may end up in the work or mobile fields if they are empty after all other phone numbers for that custid have been inserted.
How do I build that logic?
