# sql server - Summation of different rows and different columns by SQL

In the following table I am going to calculate column S:

Week L C1 C2 C3 C4 C5 C6 S

1 L1=3 258 141 140 21 51 197 S1=1000

2 7 291 261 64 275 72 249

3 1 287 164 99 222 149 172

4 3 163 193 A=200 95 170 51

5 4 193 11 271 B=300 199 26

6 L6=2 213 120 53 210 C=400 43 S6=507

7 5 36 208 243 163 126 D=100

8 3 174 56 282 274 214 222

9 1 63 295 M=222 153 136 173

10 4 191 114 195 N=8 28 188

11 3 125 293 38 56 O=277 11

As a general formula I want to find column S based on the below equations:

If L >= 3, S = C3 in three weeks later + C4 in four weeks later + C5 in five weeks later + C6 in six weeks later. For example S1 = A + B + c + D = 1000

If L < 3, S = C3 in three weeks later + C4 in four weeks later + C5 in five weeks later. For example S6= M + N + O = 507.

Thank you very much for your help.

The first got in my mind:

SELECT c1.*,
CASE WHEN c1.L >=3 THEN ISNULL(c2.C3,0) + ISNULL(c3.C4,0) + ISNULL(c4.C5,0) + ISNULL(c5.C6,0)
ELSE ISNULL(c2.C3,0) +ISNULL(c3.C4,0) + ISNULL(c4.C5,0) END as S
FROM YourTableName c1
LEFT JOIN YourTableName c2
ON c1.[Week] + 3 = c2.[week]
LEFT JOIN YourTableName c3
ON c2.[Week] + 1 = c3.[week]
LEFT JOIN YourTableName c4
ON c3.[Week] + 1 = c4.[week]
LEFT JOIN YourTableName c5
ON c4.[Week] + 1 = c5.[week]

Output:

Week    L   C1  C2  C3  C4  C5  C6  S
1       3   258 141 140 21  51  197 1000
2       7   291 261 64  275 72  249 829
3       1   287 164 99  222 149 172 430
4       3   163 193 200 95  170 51  841
5       4   193 11  271 300 199 26  474
6       2   213 120 53  210 400 43  507
7       5   36  208 243 163 126 100 251
8       3   174 56  282 274 214 222 38
9       1   63  295 222 153 136 173 0
10      4   191 114 195 8   28  188 0
11      3   125 293 38  56  277 11  0

EDIT:

For SQL Server 2012 an onward you can use LEAD

SELECT  *,
CASE WHEN L >= 3 THEN
LEAD(C3, 3, 0) OVER (ORDER BY [Week]) +
LEAD(C4, 4, 0) OVER (ORDER BY [Week]) +
LEAD(C5, 5, 0) OVER (ORDER BY [Week]) +
LEAD(C6, 6, 0) OVER (ORDER BY [Week])
ELSE
LEAD(C3, 3, 0) OVER (ORDER BY [Week]) +
LEAD(C4, 4, 0) OVER (ORDER BY [Week]) +
LEAD(C5, 5, 0) OVER (ORDER BY [Week])
END as S
FROM YourTableName

Output will be the same.