Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cursor (I think) but need help

Author  Topic 

zwheeler
Starting Member

25 Posts
Posted - 2013-08-28 : 09:28:07
Hi
I table with 132 columns, the number of rows vary based on criteria. Each column represents a financial transaction code. So, for each column i need to get the Max Value, Min Value, Mean Value and Median Value. I have the formulas for each. I can do it by manually adding each column which is problematic. So i created a table with just the column names. I created a cursor (so i could loop through the column names one at a time, pass in the column name.

thought process: if i could pass in each column name in a loop then i can pass in the name to my select into statement (add each row to another table that will contain all of my statistics) however, i keep running into error(s) because it does not recognize my column name and i am not sure if i need another cursor as well.

See Code Below (You Help and Insight would be greatly appreciated)


DECLARE @Column_Name_Metric varchar(50)
DECLARE @Metric_Value decimal(15,2)
declare @getMetricValue CURSOR
DECLARE @getName CURSOR
SET @getName = CURSOR FOR
SELECT column_name 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'temp_prt' 
order by column_name
OPEN @getName
FETCH NEXT
FROM @getName INTO @Column_Name_Metric
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Column_Name_Metric ----> this gets me all of the columns 


--Add to result set to table
	INSERT INTO stg_prt_statistics
	select @Column_Name_Metric,max(@Column_Name_Metric) as maxi,min(@Column_Name_Metric) as mini, avg(@Column_Name_Metric) as meani,(
													  (SELECT MAX(@Column_Name_Metric) FROM
														(SELECT TOP 50 PERCENT @Column_Name_Metric FROM temp_prt ORDER BY @Column_Name_Metric) AS BottomHalf)
													  +
													  (SELECT MIN(@Column_Name_Metric) FROM
														(SELECT TOP 50 PERCENT @Column_Name_Metric FROM temp_prt ORDER BY @Column_Name_Metric DESC) AS TopHalf)
													) / 2.0 AS Median
		from temp_prt

FETCH NEXT
FROM @getName INTO @Column_Name_Metric
END
CLOSE @getName
DEALLOCATE @getName

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2013-08-28 : 12:06:31
It sounds like you have an architectural issue. Having 132 columns to represent transaction codes, seems like a rather bad idea. That aside, can you provide some sample data and your expected output? Do you just need to min, mean and median for each column?

Here are some links that can help you prepare your DDL, DML and expected output:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

zwheeler
Starting Member

25 Posts
Posted - 2013-08-28 : 13:28:05
Hi

Yes, i need to get the min,max,mean, and median for each column.

Here is the ddl, for the data i will provide only about 7 columns (132 is a lot) and 4 rows of data

--DDL
[Code]CREATE TABLE [dbo].[temp_prt](
[mtr_unit_count] [numeric](17, 0) NULL,
[pk_afs_id] [numeric](31, 21) NULL,
[fk_afs_header_id] [numeric](30, 21) NULL,
[fk_afs_project_id] [numeric](31, 21) NULL,
[mtr_3247] [numeric](31, 21) NULL,
[mtr_3248] [numeric](31, 21) NULL,
[mtr_3249] [numeric](31, 21) NULL,
[mtr_3250] [numeric](31, 21) NULL,
[mtr_5000t] [numeric](31, 21) NULL,
[mtr_5060n] [numeric](31, 21) NULL,
[mtr_5060t] [numeric](31, 21) NULL,
[mtr_5100t] [numeric](31, 21) NULL,
[mtr_5120] [numeric](31, 21) NULL,
[mtr_5121] [numeric](31, 21) NULL,
[mtr_5140] [numeric](31, 21) NULL,
[mtr_5152n] [numeric](31, 21) NULL,
[mtr_5170] [numeric](31, 21) NULL,
[mtr_5180] [numeric](31, 21) NULL,
[mtr_5190] [numeric](31, 21) NULL,
[mtr_5191] [numeric](31, 21) NULL,
[mtr_5192] [numeric](31, 21) NULL,
[mtr_5193] [numeric](31, 21) NULL,
[mtr_5194] [numeric](31, 21) NULL,
[mtr_5195] [numeric](31, 21) NULL,
[mtr_5200T] [numeric](31, 21) NULL,
[mtr_5220] [numeric](31, 21) NULL,
[mtr_5240] [numeric](31, 21) NULL,
[mtr_5250] [numeric](31, 21) NULL,
[mtr_5270] [numeric](31, 21) NULL,
[mtr_5290] [numeric](31, 21) NULL,
[mtr_5300] [numeric](31, 21) NULL,
[mtr_5400T] [numeric](31, 21) NULL,
[mtr_5410] [numeric](31, 21) NULL,
[mtr_5430] [numeric](31, 21) NULL,
[mtr_5440] [numeric](31, 21) NULL,
[mtr_5490] [numeric](31, 21) NULL,
[mtr_5900T] [numeric](31, 21) NULL,
[mtr_5910] [numeric](31, 21) NULL,
[mtr_5920] [numeric](31, 21) NULL,
[mtr_5945] [numeric](31, 21) NULL,
[mtr_5960] [numeric](31, 21) NULL,
[mtr_5970] [numeric](31, 21) NULL,
[mtr_5990] [numeric](31, 21) NULL,
[mtr_6000T] [numeric](31, 21) NULL,
[mtr_6203] [numeric](31, 21) NULL,
[mtr_6204] [numeric](31, 21) NULL,
[mtr_6210] [numeric](31, 21) NULL,
[mtr_6235] [numeric](31, 21) NULL,
[mtr_6250] [numeric](31, 21) NULL,
[mtr_6263T] [numeric](31, 21) NULL,
[mtr_6310] [numeric](31, 21) NULL,
[mtr_6311] [numeric](31, 21) NULL,
[mtr_6312] [numeric](31, 21) NULL,
[mtr_6320] [numeric](31, 21) NULL,
[mtr_6330] [numeric](31, 21) NULL,
[mtr_6331] [numeric](31, 21) NULL,
[mtr_6340] [numeric](31, 21) NULL,
[mtr_6350] [numeric](31, 21) NULL,
[mtr_6351] [numeric](31, 21) NULL,
[mtr_6370] [numeric](31, 21) NULL,
[mtr_6390] [numeric](31, 21) NULL,
[mtr_6420] [numeric](31, 21) NULL,
[mtr_6450] [numeric](31, 21) NULL,
[mtr_6451] [numeric](31, 21) NULL,
[mtr_6452] [numeric](31, 21) NULL,
[mtr_6453] [numeric](31, 21) NULL,
[mtr_6400T] [numeric](31, 21) NULL,
[mtr_6510] [numeric](31, 21) NULL,
[mtr_6515] [numeric](31, 21) NULL,
[mtr_6520] [numeric](31, 21) NULL,
[mtr_6521] [numeric](31, 21) NULL,
[mtr_6525] [numeric](31, 21) NULL,
[mtr_6530] [numeric](31, 21) NULL,
[mtr_6531] [numeric](31, 21) NULL,
[mtr_6546] [numeric](31, 21) NULL,
[mtr_6548] [numeric](31, 21) NULL,
[mtr_6570] [numeric](31, 21) NULL,
[mtr_6590] [numeric](31, 21) NULL,
[mtr_6500T] [numeric](31, 21) NULL,
[mtr_6710] [numeric](31, 21) NULL,
[mtr_6711] [numeric](31, 21) NULL,
[mtr_6720] [numeric](31, 21) NULL,
[mtr_6721] [numeric](31, 21) NULL,
[mtr_6722] [numeric](31, 21) NULL,
[mtr_6723] [numeric](31, 21) NULL,
[mtr_6790] [numeric](31, 21) NULL,
[mtr_6700T] [numeric](31, 21) NULL,
[mtr_6820] [numeric](31, 21) NULL,
[mtr_6825] [numeric](31, 21) NULL,
[mtr_6830] [numeric](31, 21) NULL,
[mtr_6840] [numeric](31, 21) NULL,
[mtr_6845] [numeric](31, 21) NULL,
[mtr_6850] [numeric](31, 21) NULL,
[mtr_6890] [numeric](31, 21) NULL,
[mtr_6800T] [numeric](31, 21) NULL,
[mtr_6900] [numeric](31, 21) NULL,
[mtr_6600] [numeric](31, 21) NULL,
[mtr_6610] [numeric](31, 21) NULL,
[mtr_7105] [numeric](31, 21) NULL,
[mtr_7110] [numeric](31, 21) NULL,
[mtr_7115] [numeric](31, 21) NULL,
[mtr_7120] [numeric](31, 21) NULL,
[mtr_7130] [numeric](31, 21) NULL,
[mtr_7140] [numeric](31, 21) NULL,
[mtr_7141] [numeric](31, 21) NULL,
[mtr_7142] [numeric](31, 21) NULL,
[mtr_7190] [numeric](31, 21) NULL,
[mtr_7100T] [numeric](31, 21) NULL,
[mtr_S1000_010] [numeric](31, 21) NULL,
[mtr_S1000_020] [numeric](31, 21) NULL,
[mtr_S1000_030] [numeric](31, 21) NULL,
[mtr_S1000_040] [numeric](31, 21) NULL,
[mtr_fass_score] [numeric](3, 0) NULL,
[mtr_grs_rent_unit] [numeric](31, 21) NULL,
[mtr_vacancy_rate] [numeric](16, 9) NULL,
[mtr_eff_rent_unit] [numeric](31, 21) NULL,
[mtr_othr_inc_unit] [numeric](31, 21) NULL,
[mtr_op_expenses_per_unit] [numeric](16, 6) NULL,
[mtr_net_op_income_per_unit] [numeric](15, 8) NULL,
[mtr_debt_service_coverage] [numeric](12, 5) NULL
)[/Code]

--- DML and sample data
insert into temp_prt (mtr_unit_count,pk_afs_id,8fk_afs_header_id,7fk_afs_project_id,6mtr_3247,5mtr_3248,4mtr_3250,3mtr_5000t,2mtr_5060n)
values (131,80.3791348600508,34459.2875318066,2602938.9312977,-73.4637404580152,0,0,-73.4637404580152,466.039440203562)
insert into temp_prt (mtr_unit_count,pk_afs_id,8fk_afs_header_id,7fk_afs_project_id,6mtr_3247,5mtr_3248,4mtr_3250,3mtr_5000t,2mtr_5060n)
values (85,25.1019607843137,72715.6862745098,4207666.66666666,-57.8333333333333,0,0,-57.8333333333333,399.425490196078)
insert into temp_prt (mtr_unit_count,pk_afs_id,8fk_afs_header_id,7fk_afs_project_id,6mtr_3247,5mtr_3248,4mtr_3250,3mtr_5000t,2mtr_5060n)
values (173,113.623795761078,195168.59344894,4138641.6184971,-78.2129094412331,0,0,-78.2129094412331,401.440751445086)
insert into temp_prt (mtr_unit_count,pk_afs_id,8fk_afs_header_id,7fk_afs_project_id,6mtr_3247,5mtr_3248,4mtr_3250,3mtr_5000t,2mtr_5060n)
values (151,32.0358719646799,108261.589403973,4090960.26490066,-52.365894039735,0,0,-52.365894039735,463.893487858719)



--Expected out put for just 1 column (although i need similar result for each column except mtr_count

select 'mtr_5120',max(mtr_5120) as maxi,min(mtr_5120) as mini, avg(mtr_5120) as meani,(
(SELECT MAX(mtr_5120) FROM
(SELECT TOP 50 PERCENT mtr_5120 FROM temp_prt ORDER BY mtr_5120) AS BottomHalf)
+
(SELECT MIN(mtr_5120) FROM
(SELECT TOP 50 PERCENT mtr_5120 FROM temp_prt ORDER BY mtr_5120 DESC) AS TopHalf)
) / 2.0 AS Median
from temp_prt


produces



metric_name    maxi    mini   meani    median
mtr_5120       357.43  338.01 347.11   346.50
Go to Top of Page

zwheeler
Starting Member

25 Posts
Posted - 2013-08-29 : 18:24:08
Thanks here is my solution to the problems


--define my outer loop
DECLARE @getName CURSOR,
@columnname varchar(35),
@sql nvarchar(2000);

SET @getName = CURSOR FOR
SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'temp_prt'
order by column_name
OPEN @getName
FETCH NEXT
FROM @getName INTO @columnname
WHILE @@FETCH_STATUS = 0
BEGIN
--this is my inner loop

DECLARE @getMetric CURSOR,
@column_name varchar(35),
@metric_min decimal(12,3),
@metric_max decimal(12,3),
@metric_avg_mean decimal(12,3),
@metric_median decimal(12,3);
select @sql= N'SET @getMetric = CURSOR FOR select '
+ N' + min(' + @columnname + N' ),'
+ N' + max(' + @columnname + N' ),'
+ N' + avg(' + @columnname + N' ),'
+ N'((SELECT MAX(' + @columnname + N') FROM (SELECT TOP 50 PERCENT ' + @columnname
+ ' FROM temp_prt ORDER BY ' + @columnname + N') AS BottomHalf) + (SELECT MIN(' + @columnname
+ ') FROM (SELECT TOP 50 PERCENT ' + @columnname
+ ' FROM temp_prt ORDER BY ' + @columnname
+ N' DESC) AS TopHalf)) / 2.0'

+ N' from temp_prt '
+ N';OPEN @getMetric';



exec sp_executesql @sql,N'@getMetric cursor output',@getMetric output
FETCH NEXT
FROM @getMetric INTO @metric_min,@metric_max,@metric_avg_mean,@metric_median
WHILE @@FETCH_STATUS = 0
BEGIN
insert into prt_metric_test
(tag_column_name,mtr_min,mtr_max,mtr_avg,mtr_median)
values
(@columnname,@metric_min,@metric_max,@metric_avg_mean,@metric_median)

FETCH NEXT
FROM @getMetric INTO @metric_min,@metric_max,@metric_avg_mean,@metric_median
END
CLOSE @getMetric
DEALLOCATE @getMetric

--Close my outer loop
FETCH NEXT
FROM @getName INTO @columnname
END
CLOSE @getName
DEALLOCATE @getName

select * from prt_metric_test

which produces


pk_id tag_column_name mtr_min mtr_max mtr_median mtr_avg
1 fk_afs_header_id 34459.288 195168.593 90488.638 102651.289
2 fk_afs_project_id 2602938.931 4207666.667 4114800.942 3760051.870
3 mtr_3247 -78.213 -52.366 -65.649 -65.469
4 mtr_3248 0.000 0.000 0.000 0.000
5 mtr_3249 0.000 0.000 0.000 0.000
6 mtr_3250 -78.213 -52.366 -65.649 -65.469
7 mtr_5000t 399.425 466.039 432.667 432.700
8 mtr_5060n -121.260 -60.362 -75.816 -83.313
9 mtr_5060t -65.211 37.793 -3.221 -8.465
10 mtr_5100t 338.242 357.428 346.497 347.166
11 mtr_5120 338.013 357.428 346.497 347.109
12 mtr_5121 0.000 0.000 0.000 0.000
13 mtr_5140 0.000 0.000 0.000 0.000
14 mtr_5152n 324.812 349.811 340.591 338.951
15 mtr_5170 0.000 0.229 0.000 0.057
16 mtr_5180 0.000 0.000 0.000 0.000
17 mtr_5190 0.000 0.000 0.000 0.000
18 mtr_5191 0.000 0.000 0.000 0.000
19 mtr_5192 0.000 0.000 0.000 0.000
20 mtr_5193 0.000 0.000 0.000 0.000
21 mtr_5194 0.000 0.000 0.000 0.000
22 mtr_5195 0.000 0.000 0.000 0.000
23 mtr_5200T 1.926 18.156 6.389 8.215
24 mtr_5220 0.000 0.000 0.000 0.000
25 mtr_5240 0.000 0.000 0.000 0.000
26 mtr_5250 0.000 0.000 0.000 0.000
27 mtr_5270 0.000 0.000 0.000 0.000
28 mtr_5290 1.926 18.156 6.389 8.215
29 mtr_5300 0.000 0.000 0.000 0.000
30 mtr_5400T 0.011 0.189 0.043 0.072
31 mtr_5410 0.000 0.000 0.000 0.000
32 mtr_5430 0.000 0.000 0.000 0.000
33 mtr_5440 0.005 0.044 0.020 0.022
34 mtr_5490 0.004 0.176 0.008 0.049
35 mtr_5900T 49.567 132.947 96.097 93.677
36 mtr_5910 0.000 0.774 0.105 0.246
37 mtr_5920 2.592 3.421 3.121 3.064
38 mtr_5945 0.000 0.000 0.000 0.000
39 mtr_5960 0.000 0.000 0.000 0.000
40 mtr_5970 0.000 0.000 0.000 0.000
41 mtr_5990 46.095 130.196 92.590 90.368
42 mtr_6000T 363.648 489.763 455.624 441.165
43 mtr_6203 0.000 0.000 0.000 0.000
44 mtr_6204 0.000 0.000 0.000 0.000
45 mtr_6210 0.000 0.368 0.024 0.104
46 mtr_6235 0.000 0.000 0.000 0.000
47 mtr_6250 0.000 0.000 0.000 0.000
48 mtr_6263T 91.278 185.724 119.805 129.153
49 mtr_6310 0.000 0.000 0.000 0.000
50 mtr_6311 21.765 61.617 27.361 34.526
51 mtr_6312 0.000 0.000 0.000 0.000
52 mtr_6320 16.240 17.490 17.030 16.947
53 mtr_6330 31.801 59.924 35.690 40.776
54 mtr_6331 0.000 0.000 0.000 0.000
55 mtr_6340 1.571 21.807 13.896 12.792
56 mtr_6350 3.217 6.546 3.925 4.403
57 mtr_6351 6.638 15.166 11.122 11.012
58 mtr_6370 3.175 19.018 6.088 8.592
59 mtr_6390 0.000 0.000 0.000 0.000
60 mtr_6400T 36.717 99.637 59.272 63.724
61 mtr_6420 0.000 0.000 0.000 0.000
62 mtr_6450 3.474 54.379 20.901 24.914
63 mtr_6451 13.543 22.822 16.435 17.309
64 mtr_6452 0.000 18.807 7.131 8.267
65 mtr_6453 10.420 15.263 13.627 13.234
66 mtr_6500T 44.809 70.276 57.780 57.661
67 mtr_6510 0.000 0.000 0.000 0.000
68 mtr_6515 0.000 0.000 0.000 0.000
69 mtr_6520 0.000 0.000 0.000 0.000
70 mtr_6521 0.000 0.000 0.000 0.000
71 mtr_6525 18.156 39.175 22.736 25.701
72 mtr_6530 3.150 5.818 3.525 4.004
73 mtr_6531 0.000 0.000 0.000 0.000
74 mtr_6546 0.000 0.000 0.000 0.000
75 mtr_6548 0.000 0.000 0.000 0.000
76 mtr_6570 0.000 0.000 0.000 0.000
77 mtr_6590 16.178 42.700 26.473 27.956
78 mtr_6600 41.903 108.695 59.817 67.558
79 mtr_6610 6.738 7.793 7.316 7.291
80 mtr_6700T 16.715 29.354 18.246 20.640
81 mtr_6710 0.460 2.070 1.614 1.440
82 mtr_6711 2.236 4.308 2.837 3.054
83 mtr_6720 5.372 6.959 6.052 6.109
84 mtr_6721 0.000 0.000 0.000 0.000
85 mtr_6722 1.695 3.450 2.090 2.331
86 mtr_6723 1.761 9.349 2.486 4.021
87 mtr_6790 2.304 4.724 3.857 3.685
88 mtr_6800T 148.294 201.648 165.002 169.986
89 mtr_6820 113.447 138.041 123.775 124.760
90 mtr_6825 0.000 0.000 0.000 0.000
91 mtr_6830 22.203 42.094 25.806 28.977
92 mtr_6840 0.000 0.000 0.000 0.000
93 mtr_6845 0.000 0.000 0.000 0.000
94 mtr_6850 8.748 16.282 11.161 11.838
95 mtr_6890 2.534 5.590 4.761 4.412
96 mtr_6900 0.000 0.000 0.000 0.000
97 mtr_7100T -63.426 0.045 -3.998 -17.844
98 mtr_7105 0.000 63.426 4.008 17.861
99 mtr_7110 0.000 0.000 0.000 0.000
100 mtr_7115 0.000 0.000 0.000 0.000
101 mtr_7120 0.000 0.000 0.000 0.000
102 mtr_7130 0.000 0.000 0.000 0.000
103 mtr_7140 0.000 0.000 0.000 0.000
104 mtr_7141 0.000 0.000 0.000 0.000
105 mtr_7142 0.000 0.000 0.000 0.000
106 mtr_7190 0.000 0.045 0.010 0.016
107 mtr_debt_service_coverage 0.492 1.249 0.925 0.898
108 mtr_eff_rent_unit 324.812 349.811 340.591 338.951
109 mtr_fass_score NULL NULL NULL NULL
110 mtr_grs_rent_unit 338.242 357.428 346.497 347.166
111 mtr_net_op_income_per_unit NULL NULL NULL NULL
112 mtr_op_expenses_per_unit NULL NULL NULL NULL
113 mtr_othr_inc_unit 49.615 132.958 96.211 93.749
114 mtr_S1000_010 13.299 18.454 15.035 15.455
115 mtr_S1000_020 1.511 8.333 7.225 6.073
116 mtr_S1000_030 2.926 19.760 14.509 12.926
117 mtr_S1000_040 0.000 0.000 0.000 0.000
118 mtr_unit_count 85.000 173.000 141.000 135.000
119 mtr_vacancy_rate 0.006 0.053 0.018 0.024
120 pk_afs_id 25.102 113.624 56.208 62.785
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site