Plz tel me easier way 2 solve this problem

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

Plz tel me easier way 2 solve this problem

Author

Topic

raky
Aged Yak Warrior

767 Posts

Posted - 2008-01-07 : 07:49:34

Hi,

I have a table load which has load value for each hour.ie load_1,load_2...load_24... I want to find the max value between the 24 hourly loads and assign it to a variable say load_max...

Format of table
load_ID load_1 load_2 load_3 load_4 load_5 load_6...... load_24
1 2 4 5 6 7 8 23 56 44 22 64 33 67 24 345 34 75 57 24 23 24 24 66 789

These are the 24 load values with the load _id
I have lots of rows with load_id starting from 1- 100
Output should be to display the load_Id,load_max, load_min for each row...(after comparing the 24 loads with each other)
How can I do it with sql server.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-07 : 07:51:47

Same question from same poster ? ?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=95025

Did you already got the answer over there ?

KH
[spoiler]Time is always against us[/spoiler]

raky
Aged Yak Warrior

767 Posts

Posted - 2008-01-07 : 08:13:24

quote:
Originally posted by khtan

Same question from same poster ? ?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=95025

Did you already got the answer over there ?

KH
[spoiler]Time is always against us[/spoiler]

plz tel me easier way 2 sove this problem

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-07 : 08:32:00

Normalize your data. There is no easy way of doing what you want if your data is not normalized.

KH
[spoiler]Time is always against us[/spoiler]

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-07 : 17:57:54

Is this what you are looking for?


declare @test table (
load_ID int,
load_1 int,
load_2 int,
load_3 int,
load_4 int,
load_5 int )

insert @test (load_ID, load_1, load_2, load_3, load_4, load_5)
select 1, 23, 24, 24, 66, 789 union all
select 2, 1, 66, 42, 66, 9 union all
select 3, 2, 45, 2, 165, 57 union all
select 4, 33, 42, 857, 45, 95

--Unpivot the table.

SELECT  unpvt1.load_ID, unpvt1.load_nbr AS max_load_nbr, unpvt1.load_val AS max_load_val, unpvt2.load_nbr AS min_load_nbr, unpvt2.load_val AS min_load_val
FROM  ( SELECT  load_ID, MAX(load_val) AS max_load_val, MIN(load_val) AS min_load_val
        FROM    ( SELECT load_ID, load_nbr, load_val
                  FROM ( SELECT load_ID, load_1, load_2, load_3, load_4, load_5
                         FROM   @test ) t
                       UNPIVOT ( load_val FOR load_nbr IN ( load_1, load_2, load_3, load_4, load_5 ) )AS unpvt ) a
                  GROUP BY load_ID ) loadmax
        JOIN ( SELECT load_ID, load_nbr, load_val
               FROM ( SELECT load_ID, load_1, load_2, load_3, load_4, load_5
                      FROM   @test ) t
                    UNPIVOT ( load_val FOR load_nbr IN ( load_1, load_2, load_3, load_4, load_5 ) )AS unpvt ) unpvt1 ON loadmax.load_ID = unpvt1.load_ID
                                                                                                                        AND loadmax.max_load_val = unpvt1.load_val
        JOIN ( SELECT load_ID, load_nbr, load_val
               FROM ( SELECT load_ID, load_1, load_2, load_3, load_4, load_5
                      FROM   @test ) t
                    UNPIVOT ( load_val FOR load_nbr IN ( load_1, load_2, load_3, load_4, load_5 ) )AS unpvt ) unpvt2 ON loadmax.load_ID = unpvt2.load_ID
                                                                                                                        AND loadmax.min_load_val = unpvt2.load_val