April 10, 2007 at 9:10 am
Hi All,
I want to see what the best approach to accomplish the following situation:
I have a parent child relationship for two tables:
Program Info - Master Table
Order entry Info - Child Table
I have a flag that tells me this is the current quarter. However when a new quarter arrives I have to copy the existing quarter data and copy the data onto itself (Master table and child table) and set the flag to current quarter for the new data.
In loading the master table it's a simple
INSERT INTO master_table(field1,field2)
SELECT field1,field2 FROM master_table WHERE current_flag = 1
Update master_table SET current_flag = 0 WHERE quarter = 'past quarter'
This copies the current quarter data into the next quarter data (methodology for flag; you can only modify current quarter data and past quarter data becomes read-only)
Now this is where I'm stuck, I've now generated new master ids that need to tie into the copied child data.
I'm need to copy the same data in the child table to itself and link the new master ids to the new child data ids
How can I attack this issue?
Any ideas or thoughts?
Thanks,
April 10, 2007 at 10:02 am
A couple things, first, why copy the current quarter data? Why not just update the flag?
I'm also confused about new master ids. Do you mean you inserted new rows into the master table with that statement? If so, then you need a way to keep track of which old row is which new row. A surrogate key or some loop that will insert the parent and child together.
Can you give us an idea of the PK and FK structure the the parent and child? Don't need all fields.
April 10, 2007 at 10:20 am
The reason for copying data is that the business dictates that the data be capture at a point in time (in this case every quarter for each year). In the UI there is a drop down where the user selects the different quarters and if the flag is not set to current plan then the grid display is read-only and if the flag is set to current plan then the user can modify the data.
My primary keys are:
Program table contains Program_Id AS identity
Orders table contains Order_Id AS identity
Foreign keys:
Orders table contains Program_Id AS Int
I created a column call old_program_Id, my thought process behind this when I insert the new quarter records I insert the existing program_ids to the old_program_id to keep track of the old and new records, then I create a loop for my child records within the loop I can add the program ids and use the old program id in my where clause to properly link both parent and child.
What are your thoughts?
Thanks,
April 11, 2007 at 2:28 am
You can work out what the current quarter is by using the current date. You can then use that information to determine whether the data is read-only or not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply