Calculate column date value from variable and other column date offset-CodePudding

I have a project management table with 1000 tasks/records, each with a dynamic starting_date and ending_date column. The values of those dates are calculated by taking a static project "going-live" date (e.g. 2022-10-01) and then deducting the task_offset_days value of each record (e.g. -211), resulting in the starting_date value. The end date is calculated via a days_to_complete column with e.g. 60 days value, which if added to the starting_date gives me the final ending_date.

Example: Live date = 2022-10-01, offset by -211 days results in starting_date = '2022-03-04', which plus days_offset of 60 results in ending_date = 2022-05-03.

Here the issue:

I want to copy the 1000 records to another table and during the process want to update the starting_date, ending_date value based on another going-live date. How can I do this in the most efficient way considering I am copying and editing 1000 records?

Current copy query:

@live_date = '2022-10-01';   // going-live date

INSERT INTO `new_task_table` (
    `property_id`,  // unique identifier, not present in original table
    `status`,
    `task_desc`,
    `phase`,
    `workstream`,
    `task_level`,
    `task_owner_group`,
    `start_date`,   // = live date - offset
    `end_date`,     // = start date   days to complete
    `offset_days`,  // eg. -211 (note: can be below / above zero: -211 or 120)
    `days_to_complete`, // eg. 60
    `created_at`
)
SELECT 
    '31',
    `status`,
    `task_desc`,
    `phase`,
    `workstream`,
    `task_level`,
    `task_owner_group`,
    `start_date`,
    `end_date`,
    `offset_days`,
    `days_to_complete`,
    '2022-01-01 00:00:00'   // date of query execution
FROM `old_task_table`;

CodePudding user response：

It's probably simpler than you think. You just pass the values into the SELECT

@live_date = '2022-10-01';   // going-live date

INSERT INTO `new_task_table` (
    `property_id`,  // unique identifier, not present in original table
    `status`,
    `task_desc`,
    `phase`,
    `workstream`,
    `task_level`,
    `task_owner_group`,
    `start_date`,   // = live date - offset
    `end_date`,     // = start date   days to complete
    `offset_days`,  // eg. -211 (note: can be below / above zero: -211 or 120)
    `days_to_complete`, // eg. 60
    `created_at`
)
SELECT 
    '31',
    `status`,
    `task_desc`,
    `phase`,
    `workstream`,
    `task_level`,
    `task_owner_group`,
    DATE_ADD(@live_date, INTERVAL offset_days DAY) AS start_date,
    DATE_ADD(@live_date,INTERVAL offset_days   days_to_complete DAY) AS end_date,
    `offset_days`,
    `days_to_complete`,
    '2022-01-01 00:00:00'   // date of query execution
FROM `old_task_table`;