I have a SQL Server 2000 table (TableA) with the following fields: ItemNumber, Year, CostPeriod1, CostPeriod2...CostPeriod12
Some months will have no data, for those months I would like to look at the previous period and insert whatever data they had in the previous period into the current one. If the previous period does not have any data then go with the value in a specific table (TableB), TableB has the itemnumber and the default cost to be used when all else fails.
Let's say I'm in the field CostPeriod1 of 2010 and there is no data, how do I go back to CostPeriod12 of 2009? Or I'm in CostPeriod3 of 2010 and I want to get the data from CostPeriod2 of 2010.
How can I do this? I am using a DTS to populate the data.
Thank you in advance!