Multiply by a large string and add to the list
I have the following in two tables:
(SFR_MAIN)
SQ. FT. AREA 1 2
400 86.6600 86.7300
500 82.3300 82.4000
600 78.9600 79.0200
700 76.2100 76.2700
800 73.9100 73.9700
900 71.9400 71.9900
1000 70.2200 70.2700
1100 68.7000 68.7500
1200 67.3400 67.3900
1300 66.1200 66.1600
1400 65.0000 65.0400
1600 63.0400 63.0800
1800 61.3600 61.3900
2000 59.8900 59.9300
2200 58.6000 58.6300
2400 57.4400 57.4700
2600 56.3900 56.4200
2800 55.4400 55.4700
3000 54.5700 54.6000
and
(MULT)
SQ. FT. AREA MULTIPLIER
3100 0.992
3200 0.986
3300 0.978
3400 0.971
3500 0.964
3600 0.958
3700 0.952
3800 0.946
4000 0.934
The idea would be to create a table with both. The problem is that the second is a multiplier that should be multiplied by the last (largest) squarfootage in the first.
So 3100 will be .992 * 54.57
for the column 1
and .992 * 54.6
for the column 2
, yes these are the actual column names.
So the desired output is:
SQ. FT. AREA 1 2
400 86.6600 86.7300
500 82.3300 82.4000
600 78.9600 79.0200
700 76.2100 76.2700
800 73.9100 73.9700
900 71.9400 71.9900
1000 70.2200 70.2700
1100 68.7000 68.7500
1200 67.3400 67.3900
1300 66.1200 66.1600
1400 65.0000 65.0400
1600 63.0400 63.0800
1800 61.3600 61.3900
2000 59.8900 59.9300
2200 58.6000 58.6300
2400 57.4400 57.4700
2600 56.3900 56.4200
2800 55.4400 55.4700
3000 54.5700 54.6000
3100 54.1334 54.1632
3200 53.8060 53.8356
3300 53.3695 53.3988
3400 52.9875 53.0166
3500 52.6055 52.6344
3600 52.2781 52.3068
3700 51.9506 51.9792
3800 51.6232 51.6516
4000 50.9684 50.9964
I can make one column like this:
select
[SQ. FT. AREA],
[1]
from test.dbo.SFR_MAIN
union all
Select
[SQ. FT. AREA],
MULTIPLIER * (select
m.[1]
from test.dbo.SFR_MAIN m
inner join
(
select max([SQ. FT. AREA]) as mmm
from test.dbo.SFR_MAIN
) tt on tt.mmm = m.[SQ. FT. AREA]
) as [1]
from test.dbo.mult
And , if I only had two columns, I would just do it twice, but I have 16 columns . Is there a way to iterate over the columns to get them all side by side in the table?
I am using SQL Server 2012
source to share
Database iteration is generally a bad idea. Databases perform better on a set basis. So union as you describe, but then cross-join in the entry from the first set that has the highest square foot, then just do the math.
SELECT [SQ. Ft. Area], [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16]
FROM sfr_Main
UNION ALL
SELECT A.[SQ. FT. Area]
, B.multiplier*[1] as 1
, B.multiplier*[2] as 2
, B.multiplier*[3] as 3
, B.multiplier*[4] as 4
, B.multiplier*[5] as 5
, B.multiplier*[6] as 6
, B.multiplier*[7] as 7
, B.multiplier*[8] as 8
, B.multiplier*[9] as 9
, B.multiplier*[10] as 10
, B.multiplier*[11] as 11
, B.multiplier*[12] as 12
, B.multiplier*[13] as 13
, B.multiplier*[14] as 14
, B.multiplier*[15] as 15
, B.multiplier*[16] as 16
FROM MULT A
CROSS JOIN (SELECT top 1 [SQ. Ft. Area], [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16]
FROM sfr_main
ORDER BY [sq.ft.area] desc) B
source to share