Home  >  Q&A  >  body text

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

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`;

P粉401901266P粉401901266429 days ago524

reply all(1)I'll reply

  • P粉909476457

    P粉9094764572023-09-08 12:04:17

    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`;

    reply
    0
  • Cancelreply