CODEWITHSUNDEEP
sqlsql-server
Ashkan Mirzaee
Ashkan Mirzaee

Reputation: 330

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.

Upvotes: 1

Views: 47

Answers (1)

gofr1
gofr1

Reputation: 15997

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.

Upvotes: 3

Related Questions