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.
| Author |
Topic |
|
sangeeta
Starting Member
16 Posts |
 Posted - 2006-01-11 : 23:59:11
|
| Hi: I am new to TSQ and need some help with structuring my query. Any help would be appreciated. My table looks as follows Record AAPL MSFT GE CISCO 1 36 19 71 89 2 88 53 40 44 3 56 22 21 75 4 59 64 90 40 5 78 70 38 57 6 89 80 71 90 I need to pull out records where the difference between the values of the 4 symbols (AAPL,MSFT,GE and CISCO) is greater than 50. The result for the given data would be: 1 36 19 71 89 70 3 56 22 21 75 54 For record1, Difference betwwen the maximum:89 and the minimum:19 is 70 For record2, Difference betwwen the maximum:75 and the minimum:21 is 54 The difference should be reflected in the last column of the result set. Thanks. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-01-12 : 00:27:29
|
| [code]select * from table1 dwhere record in ( select record from ( select record, AAPL as symbol from table1 union all select record, MSFT from table1 union all select record, GE from table1 union all select record, CISCO from table1 ) as m group by record having max(symbol) - min(symbol) > 50) [/code][EDIT]change #table to table1[/EDIT]-----------------'KH'Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. |
 |
|
|
sangeeta
Starting Member
16 Posts |
Posted - 2006-01-12 : 00:51:53
|
| Thanks Khtan -I know this might be a very stupid question but I get an error saying that "invalid object #table1".Thanks. |
|
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-01-12 : 00:57:19
|
| Replace table1 and #table 1 with your tablename |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-01-12 : 01:02:09
|
| [code]select a.*, x.Diff from MyTable a join ( select m.record, Diff = max(m.symbol)-min(m.symbol) from ( select record, AAPL as symbol from MyTable union all select record, MSFT from MyTable union all select record, GE from MyTable union all select record, CISCO from MyTable ) as m group by m.record having max(m.symbol)-min(m.symbol) > 50 ) x on a.record = x.record[/code]CODO ERGO SUM |
|
|
|
sangeeta
Starting Member
16 Posts |
Posted - 2006-01-12 : 01:09:42
|
| Thanks a lot Michael and Khtan - That worked. |
|
|
|
|
|
|
|
|
tsql - exterme values