I am developing a database where I need to perform calculations on fields in a record and then append multiple records in another table based on the caluculations. For example:
Each record in an Estimate table:
Code:
est_id start_date num_of_weeks, num_per_week
1 6/4/12 4 5
gets "processed" and appended to another table like this:
Buy table
Code:
buy_id est_id week_date week_num num_per_week
1 1 6/4/12 1 5
2 1 6/11/12 2 5
3 1 6/18/12 3 5
4 1 6/25/12 4 5
In this specific case, the "processing" is using the initial start_date and num_of_weeks to build a schedule for each week of num_per_week, along with other data. Ultimately, the items with similar week_date get aggregated together.
My initial approach is to use DAO and loop through a procedure that appends each new record, incrementing (week_date + 7), and (week_num + 1) from 1 to num_of_weeks.
But I wanted to be a little humble and ask for thoughts on the easiest and most efficient method to accomplish this. Any ideas?