Overview
Application Scenario
Fields in the order table of your business system include orderid, money, order transaction date, and order creation date. Under normal circumstances, order transaction date and order creation date have the same values. However, date discrepancies may occur due to supplementary entry of order data. For example, business personnel may submit data of orders that happened on 05-01 to the system on 05-10, in which case the order creation date is 05-10, and the order transaction date is 05-01.
Data on the ODS layer of a data warehouse is incrementally updated based on the order creation date and can be updated normally in case of supplementary entry.
On the DM layer, the order amount is summarized daily by order transaction date and data from five days before the order transaction date is scheduled to be incrementally updated daily. However, because the time difference between the order creation date and the order transaction date may exceed five days, the incremental update plan where old data is deleted first does not apply to the supplemented data. For example, on 05-16, data of orders created on 05-01 and 05-02 is supplemented, but the system only updates data from 05-12 to 05-16.

Implementation Method
First, perform an incremental update on data whose order transaction date is within the past five days of the current date.
Filter orders whose creation date is later than its transaction date and fetch the transaction date. Data with the fetched transaction date contains supplemented data, requiring a re-summary of the daily order amount.
Procedure
Example: An incremental update on order table data whose order transaction date is within the past five days of the current date is executed regularly. Additionally, for supplemented data whose order transaction date is beyond the time range of incremental updates, the order amount on that date is re-summarized and updated.
For example, if data of orders created on 05-01 and 05-02 is supplemented on 05-16, the system performs incremental updates on both data from 05-12 to 05-16 and the supplemented data.
Data in the ZISJ_order table before the update is shown in the following figure.

The ZISJ_order_sum table containing the summarized daily order amount is shown in the following figure.
You want to synchronize the transaction data increments within the past five days and the supplemented data to the data table. The expected effect is shown in the following figure.
Updating Data Increments of the Past Five Days
Create a scheduled task and drag a SQL Script node onto the page. Configure the script to delete the summarized daily transaction amount of the past five days from the summary table.
The following is the MySQL statement.
DELETE FROM ZJSJ_order_sum
WHERE date >= CURDATE() - INTERVAL 5 DAY;
Add a Data Synchronization node to re-summarize the daily order amount of the past five days and write it into the summary table, as shown in the following figure.
The following is the MySQL statement.
SELECT DATE (order transaction date) AS date, SUM (money) AS total_money
FROM ZJSJ_order
WHERE order transaction date >= CURDATE () - INTERVAL 5 DAY -- Select the records within the past five days.
GROUP BY DATE(order transaction date)
Click Data Preview to see the summarized daily sales amount of the past five days of the order transaction date, as shown in the following figure.
Write the data into the summary table, set Write Method to Write Data into Target Table Directly, select date as the logical primary key, and set Strategy for Primary Key Conflict to Overwrite Data in Target Table If Same Primary Key Value Exists, as shown in the following figure.
Fetching the Order Transaction Date of Supplemented Data and Re-summarizing the Daily Amount
Add a Parameter Assignment node to fetch the order transaction date of data whose order creation date is later than its order transaction date from the ZISJ_order table, as shown in the following figure.
The following is the MySQL statement.
SELECT DATE(order transaction date) AS jy_date_list
FROM ZJSJ_order
WHERE DATE(order creation date) > DATE(order transaction date)
GROUP BY DATE(order transaction date)
Click Data Preview, as shown in the following figure.
You can set this return value as a parameter, as shown in the following figure.

Add a Data Synchronization node, pass in the order transaction date of supplemented data to filter data with the order transaction date, and summarize the order amount of the filtered transaction date, as shown in the following figure.
The following is the MySQL statement.
SELECT DATE (order transaction date) AS date, SUM (money) AS total_money
FROM ZJSJ_order
WHERE DATE (order transaction date) IN (${new_date}) -- Specify the date.
GROUP BY DATE (order transaction date)
Click Data Preview, and you can see the data, as shown in the following figure.
Write the summarized data to the summary table, as shown in the following figure.
Running the Task
Run the task. After successful execution, the data increments of the past five days has been synchronized, and the supplemented data has been summarized and synchronized, as shown in the following figure.