开发者

SQL Query Help after 13th row Dynamic and in 12th row static values with a formula

I have below table structure with the similar to below output.

S_NO_   T_P     O_P          H_P        L_P         C_P           SC_12
1       1       509.75       515        508         512.4500122 
2       2       511.7000122 511.7000122 506.1499939 506.5499878 
3       4       507.1499939 510.25      507.1499939 510.25      
4       5       510         512.3499756 509.2999878 512.3499756 
5       3       512.5       512.5       511.1499939 512     
6       8       512.25      512.5       510.1000061 510.9500122 
7       1       510.5499878 511.7999878 510     511.7999878 
8       2       511.1000061 511.8500061 508.1499939 508.8999939 
9       5       508.8999939 510        508.5        509.9500122 
10     6       509.8999939  509.8999939 508.5       508.8500061      
11     8        509.5       511.2000122 509     510.5       
12     9        510.5       511.7999878 510.1000061 510.2000122 **510.4**
13    12        510.2999878 511.3500061 510.25      510.75      510.25
14    14        510.3500061 512     510.3500061 510.9500122 510.62
15    15        510.9500122 511.7999878 510.6000061 511.1000061 510.69
16    17        511.0499878 511.3500061 509.1000061 509.1000061 510.42
17    13        509.5       509.5       508.1000061 508.5       510.13
18    13        508.4500122 508.9500122 507         507         509.8
19    19        507         508.2000122 503.2999878 503.2999878 509.09
20    11        504         505        503.5        504.6499939 508.74
21    17        505.4500122 506.3500061  504        504.7000122 508.3
22    18        504.7000122 505.5       504.2000122 505.5       508.02
23    19        505.3500061 505.7000122 503.1000061 503.6499939 507.45

I want write a SQL Query to get results in C12WR column.

And I want on row of C12WR Column use a static value which is in “SC_12” (In the shown table the value is “510.4”) and in the SC_12 Column it should calculate the below formula after 13th row number and it continue it till the end of the table records After 13th Row in C12WR Column = (the value of above row*11 + Current row value from C_P Column) /12

The final output should be similar to below 

S_NO_   T_P     O_P         H_P         L_P     C_P                SC_12    c12-WWR
1       1       50开发者_开发问答9.75      515         508     512.4500122     
2       2       511.7000122 511.7000122 506.1499939 506.5499878                 
3       4       507.1499939 510.25      507.1499939 510.25              
4       5       510         512.3499756 509.2999878 512.3499756             
5       3       512.5       512.5       511.1499939 512         
6       8       512.25      512.5       510.1000061 510.9500122         
7       1       510.5499878 511.7999878 510     511.7999878             
8       2       511.1000061 511.8500061 508.1499939 508.8999939                 
9       5       508.8999939 510     508.5       509.9500122         
10      6       509.8999939 509.8999939 508.5       508.8500061             
11      8       509.5       511.2000122 509     510.5           
12      9       510.5       511.7999878 510.1000061 510.2000122     510.4   510.4
13      12      510.2999878 511.3500061 510.25      510.75          510.25  510.3833344
14      14      510.3500061 512     510.3500061 510.9500122         510.62  510.4138898
15      15      510.9500122 511.7999878 510.6000061 511.1000061     510.69  510.4585667
16      17      511.0499878 511.3500061 509.1000061 509.1000061     510.42  510.51202
17      13      509.5       509.5       508.1000061 508.5           510.13  510.3943521
18      18      508.4500122 508.9500122 507     507                 509.8   510.2364895
19      19      507     508.2000122 503.2999878 503.2999878         509.09  509.966782
20      11      504     505     503.5       504.6499939          508.74 509.4112158
21      17      505.4500122 506.3500061 504     504.7000122         508.3   509.0144473
22      18      504.7000122 505.5       504.2000122 505.5           508.02  508.6549111
23      19      505.3500061 505.7000122 503.1000061 503.6499939       507.45 508.3920018


I don't know what you mean by a static value which is in “SC_12” But if you have that value in the table then use this:

select T_P, O_P, H_P, L_P, C_P, SC_12
    case when t1.S_NO_ > 12 
        then cast ((t2.SC_12 * 11 + t1.C_P)/12 as varchar(50))
        else '' 
        end as [c12-WWR]
from t as t1
inner join t as t2 on t2.S_NO_ = t1.S_NO_ -1
order by t1.S_NO_
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜