SQL Server 2019

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

CREATE TABLE #initial_table (weeknum int, daynum int, value int, PRIMARY KEY (weeknum, daynum));

INSERT INTO #initial_table (weeknum, daynum, value) VALUES
(1, 1,  22),
(1, 2,  23),
(1, 3,   4),
(1, 4,  23),
(1, 5,  14),
(1, 6, 132),
(1, 7, 211),
(2, 1, 155),
(2, 2, 190),
(2, 3,  33),
(2, 4, 222),
(2, 5,  75),
(2, 6,  92),
(2, 7, 107);

SELECT * FROM #initial_table;


weeknum
daynum
value


1
1
22

1
2
23

1
3
4

1
4
23

1
5
14

1
6
132

1
7
211

2
1
155

2
2
190

2
3
33

2
4
222

2
5
75

2
6
92

2
7
107


WITH AvgList AS
(SELECT weeknum,
        daynum, 
        value, 
		SUM(value) OVER (ORDER BY weeknum, daynum) AS CumulativeSum,
        AVG (CAST(value AS decimal(8,2))) OVER (ORDER BY weeknum, daynum ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS 'Avg5',
        AVG (CAST(value AS decimal(8,2))) OVER (ORDER BY weeknum, daynum ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) AS 'Avg1',
        AVG (CAST(value AS decimal(8,2))) OVER (ORDER BY weeknum, daynum ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 'AvgOther'
    FROM #initial_table
)
SELECT weeknum,
       daynum, 
       [value],
       CASE WHEN daynum = 5 THEN [Avg5]
            WHEN daynum = 1 THEN [Avg1]
            ELSE [AvgOther] END AS hello
FROM AvgList;


weeknum
daynum
value
hello


1
1
22
22.500000

1
2
23
16.333333

1
3
4
16.666666

1
4
23
13.666666

1
5
14
95.000000

1
6
132
119.000000

1
7
211
166.000000

2
1
155
172.000000

2
2
190
126.000000

2
3
33
148.333333

2
4
222
110.000000

2
5
75
124.000000

2
6
92
91.333333

2
7
107
99.500000


SELECT weeknum,
       daynum, 
       [value],
       CASE WHEN daynum = 5 THEN AVG (CAST(value AS decimal(8,2))) OVER (ORDER BY weeknum, daynum ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
            WHEN daynum = 1 THEN AVG (CAST(value AS decimal(8,2))) OVER (ORDER BY weeknum, daynum ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING)
            ELSE  AVG(CAST(value AS decimal(8,2))) OVER (ORDER BY weeknum, daynum ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) END AS hello
FROM #initial_table;


weeknum
daynum
value
hello


1
1
22
22.500000

1
2
23
16.333333

1
3
4
16.666666

1
4
23
13.666666

1
5
14
95.000000

1
6
132
119.000000

1
7
211
166.000000

2
1
155
172.000000

2
2
190
126.000000

2
3
33
148.333333

2
4
222
110.000000

2
5
75
124.000000

2
6
92
91.333333

2
7
107
99.500000

weeknum	daynum	value
1	1	22
1	2	23
1	3	4
1	4	23
1	5	14
1	6	132
1	7	211
2	1	155
2	2	190
2	3	33
2	4	222
2	5	75
2	6	92
2	7	107

weeknum	daynum	value	hello
1	1	22	22.500000
1	2	23	16.333333
1	3	4	16.666666
1	4	23	13.666666
1	5	14	95.000000
1	6	132	119.000000
1	7	211	166.000000
2	1	155	172.000000
2	2	190	126.000000
2	3	33	148.333333
2	4	222	110.000000
2	5	75	124.000000
2	6	92	91.333333
2	7	107	99.500000

weeknum	daynum	value
1	1	22
1	2	23
1	3	4
1	4	23
1	5	14
1	6	132
1	7	211
2	1	155
2	2	190
2	3	33
2	4	222
2	5	75
2	6	92
2	7	107

weeknum	daynum	value
1	1	22
1	2	23
1	3	4
1	4	23
1	5	14
1	6	132
1	7	211
2	1	155
2	2	190
2	3	33
2	4	222
2	5	75
2	6	92
2	7	107