SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2014 (SP2-CU12) (KB4130489) - 12.0.5589.7 (X64) 
	Jun  9 2018 11:17:15 
	Copyright (c) Microsoft Corporation
	Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)



create table temptable
( RetailerID int,
  retailer nvarchar(50),
  category nvarchar(50),
  Weekid int,
  value bigint
) ;
Insert into temptable 
values
  -- a
  (34,'R1','a',1,10),
  (34,'R1','a',2,10),
  (34,'R1','a',3,4),
  (34,'R1','a',4,5),
  
  (34,'R1','a',5,1),
  (34,'R1','a',6,2),
  (34,'R1','a',7,3),
  (34,'R1','a',8,4),
  
  (34,'R1','a',9,5),
  (34,'R1','a',10,6),
  
  -- b
  (34,'R1','b',1,2),
  (34,'R1','b',2,4),
  (34,'R1','b',3,5),
  (34,'R1','b',4,3),
  
  (34,'R1','b',5,1),
  
  -- c
  (34,'R1','c',1,35),
  (34,'R1','c',2,68),
  (34,'R1','c',3,43),
  (34,'R1','c',4,4),
  
  (34,'R1','c',5,5) ;
20 rows affected

with cte as
(
select 
    RetailerID, Category, WeekId, value,
    row_number() over
        (partition by RetailerID, Category
         order by WeekId)
        as rn,
    count(*) over
        (partition by RetailerID, Category)
        as tc,  -- total count per partition
    sum(value) over
        (partition by RetailerID, Category
         order by WeekId
         rows between 3 preceding
                  and current row)
        as sv
from temptable 
where Weekid between 1 and 12 
  and RetailerID = 34
)
select
    RetailerID, Category, WeekId, value,  
    case when rn <= floor(tc / 4) * 4
        then case (rn % 4)
                 when 1 then lead(sv, 3) over (partition by RetailerID, Category order by WeekId)
                 when 2 then lead(sv, 2) over (partition by RetailerID, Category order by WeekId)
                 when 3 then lead(sv, 1) over (partition by RetailerID, Category order by WeekId)
                 else sv
             end
        else sv
    end as sum_value
from
    cte ;


RetailerID
Category
WeekId
value
sum_value


34
a
1
10
29

34
a
2
10
29

34
a
3
4
29

34
a
4
5
29

34
a
5
1
10

34
a
6
2
10

34
a
7
3
10

34
a
8
4
10

34
a
9
5
14

34
a
10
6
18

34
b
1
2
14

34
b
2
4
14

34
b
3
5
14

34
b
4
3
14

34
b
5
1
13

34
c
1
35
150

34
c
2
68
150

34
c
3
43
150

34
c
4
4
150

34
c
5
5
120

RetailerID	Category	WeekId	value	sum_value
34	a	1	10	29
34	a	2	10	29
34	a	3	4	29
34	a	4	5	29
34	a	5	1	10
34	a	6	2	10
34	a	7	3	10
34	a	8	4	10
34	a	9	5	14
34	a	10	6	18
34	b	1	2	14
34	b	2	4	14
34	b	3	5	14
34	b	4	3	14
34	b	5	1	13
34	c	1	35	150
34	c	2	68	150
34	c	3	43	150
34	c	4	4	150
34	c	5	5	120

RetailerID	Category	WeekId	value	sum_value
34	a	1	10	29
34	a	2	10	29
34	a	3	4	29
34	a	4	5	29
34	a	5	1	10
34	a	6	2	10
34	a	7	3	10
34	a	8	4	10
34	a	9	5	14
34	a	10	6	18
34	b	1	2	14
34	b	2	4	14
34	b	3	5	14
34	b	4	3	14
34	b	5	1	13
34	c	1	35	150
34	c	2	68	150
34	c	3	43	150
34	c	4	4	150
34	c	5	5	120

RetailerID	Category	WeekId	value	sum_value
34	a	1	10	29
34	a	2	10	29
34	a	3	4	29
34	a	4	5	29
34	a	5	1	10
34	a	6	2	10
34	a	7	3	10
34	a	8	4	10
34	a	9	5	14
34	a	10	6	18
34	b	1	2	14
34	b	2	4	14
34	b	3	5	14
34	b	4	3	14
34	b	5	1	13
34	c	1	35	150
34	c	2	68	150
34	c	3	43	150
34	c	4	4	150
34	c	5	5	120