Create Multiple years data from single record
Posted: Mon Jul 18, 2011 9:29 am
All,
I would really appreciate some ideas on the below requirement.
***********************************************************
Input is a single record from source, like below, with start year, end year and the total amount spent for those years:
Start_Year End_Year Amt
________________________________________
2009/07/01 2010/09/01 204.20
This needs to be transformed into the below. The spent amount is distributed between July, 2009 thru to Sep 2010 (which is 15 months in total, so Amount per month is 204.20/15 = 13.61).
All the other months need to have a value zero in the below table.
So 2 records need to be created for the 2 years and the total amount distributed equally for the 15 months. All other months not in the range will have a zero
Year Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec
____________________________________________________________________________________________________________________________
2009 0 0 0 0 0 0 13.61 13.61 13.61 13.61 13.61 13.61
2010 13.61 13.61 13.61 13.61 13.61 13.61 13.61 13.61 13.61 0 0 0
*************************************************************
Thanks in advance.
I would really appreciate some ideas on the below requirement.
***********************************************************
Input is a single record from source, like below, with start year, end year and the total amount spent for those years:
Start_Year End_Year Amt
________________________________________
2009/07/01 2010/09/01 204.20
This needs to be transformed into the below. The spent amount is distributed between July, 2009 thru to Sep 2010 (which is 15 months in total, so Amount per month is 204.20/15 = 13.61).
All the other months need to have a value zero in the below table.
So 2 records need to be created for the 2 years and the total amount distributed equally for the 15 months. All other months not in the range will have a zero
Year Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec
____________________________________________________________________________________________________________________________
2009 0 0 0 0 0 0 13.61 13.61 13.61 13.61 13.61 13.61
2010 13.61 13.61 13.61 13.61 13.61 13.61 13.61 13.61 13.61 0 0 0
*************************************************************
Thanks in advance.