One of the files, i am working with, has 7 columns, with names representing a day of the week in date format. The data in these columns is numeric and represents units for each day - similar to the below:
ProductName 7/14/2012 7/16/2012 7/17/2012
Product A 6 5 6

As you see, the 'Date' columns are dynamic and will change based on the specific week, but I will always have 7 columns representing each day in a week, so in Data Migrator flow, I need to capture day1 (field 19 for instance) and day7 and pass the values of these COLUMN NAMES as actual data to Start_date and End_date columns. Please note that I used generic column names (Fieldname1, Fieldname2, Fieldname3..etc) in a synonym, so I can isolate what are the Date values where FiledName1 = 'ProductName'. Is there a way to dynamically manage updating the start/end date columns based on source data? I was loking for some define statement that can be added to the synonym , but could not find anything useful(working).

Could you provide a more complete example of an input file? Do all of the files have just two rows?

Sorry for late response. I used another software to accomplish this. However, here is a sample data:

Location,Dept,MIC,Group,Major,Minor,Sel,Style,Style Desc,Color Code,Color Desc,Size Code,Size Desc,Primary,OH,OO,ST,Data Type,7/8/2012,7/9/2012,7/10/2012,7/11/2012,7/12/2012,7/13/2012,7/14/2012,Total
Filters -->,,,,,,,,,,,,,,,,,,,,,,,,,
ALL,243,635,REP, , ,155,12012846,PE SIL/PPW,40,SILVER,0,NO SIZE,145236958,96,0,13.5,Quantity,1,1,2,0,3,4,4,15
ALL,243,635,REP, , ,155,12012846,PE SIL/PPW,40,SILVER,0,NO SIZE,145236958,96,0,13.5,Average,14.4,4.8,14.4,0,17.6,14.64,16.8,15.1
ALL,243,635,REP, , ,150,30124,CE 14G/NF CHOOP,710,GOLD,0,NO SIZE,60498911,128,0,9.2,Quantity,2,2,2,1,2,1,3,13
ALL,243,635,REP, , ,150,30124,CE 14G/NF CHOOP,710,GOLD,0,NO SIZE,60498911,128,0,9.2,Average,20.8,20.8,15.6,15.6,15.6,26,15.6,18

Thanks!

Just saw your reply. I did this by creating two synonyms, one for the data rows ANNAR with HEADER=YES, and one for the header line ANNAH with HEADER=NO. Then joined ANNAR to a sub-select:

SELECT
T1. all columns needed...,
T2.START_DATE ,
T2.END_DATE
FROM
(annar T1 LEFT OUTER JOIN
(SELECT
'ALL' AS LOCATION ,
T1.DAY1 AS START_DATE ,
T1.DAY2 AS END_DATE
FROM
annah T1
WHERE
T1.LOCATION = 'Location'
) T2
ON
T1.LOCATION = T2.LOCATION )