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_
精彩评论