Calculate column date values: Use variables and other columns to perform date offset calculations

Question

I have a project management table with 1000 tasks/records, each task/record has a dynamic starting_date and ending_date columns. The values ​​for these dates are obtained by taking a static project "go live" date (e.g. 2022-10-01) and then subtracting each record's task_offset_days value (e.g. -211) , thereby getting the value of starting_date. The end date is calculated from a days_to_complete column which has a value of for example 60 days, if you add that to the starting_date you get the final ending_date.

Example: Online date = 2022-10-01, offset -211 days, get starting_date = '2022-03-04', and then Adding the value 60 of days_offset, we get ending_date = 2022-05-03.

The question here is:

I want to copy these 1000 records to another table and in the process update the values ​​of starting_date and ending_date based on another go-live date. Considering I have 1000 records to copy and edit, how can I do this in the most efficient way?

Current replication query:

@live_date = '2022-10-01';   // 上线日期

INSERT INTO `new_task_table` (
    `property_id`,  // 唯一标识符，在原始表中不存在
    `status`,
    `task_desc`,
    `phase`,
    `workstream`,
    `task_level`,
    `task_owner_group`,
    `start_date`,   // = 上线日期 - 偏移量
    `end_date`,     // = 开始日期 + 完成天数
    `offset_days`,  // 例如 -211（注意：可以低于/高于零：-211或120）
    `days_to_complete`, // 例如 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'   // 查询执行的日期
FROM `old_task_table`;

Answer 1

This may be simpler than you think. You just pass the value to SELECT

@live_date = '2022-10-01';   // 上线日期

INSERT INTO `new_task_table` (
    `property_id`,  // 唯一标识符，在原始表中不存在
    `status`,
    `task_desc`,
    `phase`,
    `workstream`,
    `task_level`,
    `task_owner_group`,
    `start_date`,   // = 上线日期 - 偏移量
    `end_date`,     // = 开始日期 + 完成天数
    `offset_days`,  // 例如 -211（注意：可以是负数或正数：-211 或 120）
    `days_to_complete`, // 例如 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'   // 查询执行日期
FROM `old_task_table`;