Incremental delta load

Question

I have scenario, where I need to load incremental data in target table (2016Std edition SQL Server).
We get weekly incremental data in source table and the data in the source table get added for a week and the process updates/inserts the data for entire month along with adding any additional records for current week

I have to do insert or update(based on new or old data) the target table for entire month for every weeks incremental data in source. This can be done using MERGE and the batchID, which is common in target table for entire month.

I can do merge using batchid, but when I come across a scenario where the months last weeks incremental data falls into two batchid's(month ending and new month starting), I need to identify and pick two batchid(one for previous month and other for current) in this case. This is where I need some inputs on how to handle this.

Answer 1

Hi @Neil ,

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Per my poor understanding, you could have a try to get the ISO week number of every date and combine the records which have the same ISO week number.

Please refer below a simple example and check whether it is a little helpful:

drop table if exists Incrementalload  
  
create table Incrementalload   
(BacthID Varchar(20),  
BatchDate date,  
Qty int)  
  
insert into Incrementalload values  
('A00001','2021-03-30',100),  
('A00001','2021-03-31',200),  
('A00002','2021-04-01',300),  
('A00002','2021-04-02',400),  
('A00003','2021-03-22',500),  
('A00003','2021-03-24',600)  
  
;with cte as (  
select *,DATEPART(isowk, BatchDate) isowk  
from Incrementalload)  
select isowk,sum(qty) Total  
from cte   
group by isowk  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.