Extract start date and end date from a file field
I have a requirement like this. I need to generate a report which has 2 fields start date and maturity date. I have FileA which has a date field and has some values say 12/31/2012, 01/05/2013, 10/01/2013,25/01/2013 and so on. I need to generate a report which has a field start date and maturity date. Only for first record in the report, the start date is from FileB. The maturity date for first record in report is from FileA(12/31/2012). The second record'start date is (12/31/2012) and maturity date is the second date from fileA(01/05/2013). Third record's start date is 01/05/2013 and maturity date is 3rd date from FileA(10/01/2013) and so on. How can I achieve this?
Software/Hardware used:
Software/Hardware used:
ASKED:
January 7, 2013 1:17 AM
UPDATED:
January 7, 2013 1:27 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
I’m sorry, but your description isn’t clear. First, we will need file/field descriptions for the two files, and then a description of the relationship between the two files. Other details might also be needed, but more questions can be asked after we understand the files. — Tom
Let’s see if I’ve got this right: your initial start date is from your FileB, with the maturity date coming from the first record in your FileA; your next start date is the previous maturity date, with the new maturity date coming from the next record in your FileA, repeat until the end of FileA is reached.It sounds like you need to access your FileB first, then proceed through your FileA.
If I understand .. which never seems to happen … this would be a procedural program .. rpg or cobol. It would read the necessary files and write the first record.
For each subsequent record put the current date into a workfield for Maturity date and get the next record from fileA. Prepare your output and write one record for each record from file A.
Phil
…a date field and has some values say 12/31/2012, 01/05/2013, 10/01/2013,25/01/2013
If those really are values in the file, then the field is apparently not a “date” field. It’s just a plain character field.
The fact that only the “first record” has a start date in FileB needs some explanation. Is FileB used in order to find the starting point in FileA? Does FileA need to be read through until finding the proper starting record? Or are all records in FileA and FileB always used?
It seems that every date in FileA is both a starting date and a maturity date. Record N is a starting date, and record N+1 is a corresponding maturity date. Or maybe not. The requirement isn’t clear yet.
One particularly unclear element is the ordering of the data. How is it guaranteed that start and maturity dates are properly paired? If views are used, for example, an ORDER BY needs to be shown so we know what’s going on.
Regardless, more info is needed. The design of the database seems to be enough of a problem by itself that the proper processing needs the extra info.
Tom