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.