case else help - SQL Server Forums

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

case else help

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2008-04-10 : 07:24:58

hi,



SELECT CASE WHEN data_Flag ='5' THEN Avg_Binaryvalue ELSE NULL END AS Avg1, 
CASE WHEN data_Flag ='5' THEN Min_Binaryvalue ELSE NULL END Min1,
CASE WHEN data_Flag ='6' THEN Avg_Binaryvalue ELSE NULL END AS Avg2, 
CASE WHEN data_Flag ='6' THEN Min_Binaryvalue ELSE NULL END AS Min2
FROM MeasurementData where sensor_serial_no='4' and month(date_time)='3'

this is my qrery.

i have 14 rows for data_flag =5 and 14 rows for data_flag=6
so if i use this query i am getting 26 rows.
my current output:

avg1 min1 avd2 min2

null null 10 20
23 24 null null
null null 54 57
43 12 null null
ets

my output should be like this:

avg1 min1 avd2 min2

23 24 10 20

43 12 54 57

so please give me sample query for this please

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 07:31:10

[code]SELECT max(CASE WHEN data_Flag ='5' THEN Avg_Binaryvalue ELSE NULL END) AS Avg1,
max(CASE WHEN data_Flag ='5' THEN Min_Binaryvalue ELSE NULL END) Min1,
max(CASE WHEN data_Flag ='6' THEN Avg_Binaryvalue ELSE NULL END) AS Avg2,
max(CASE WHEN data_Flag ='6' THEN Min_Binaryvalue ELSE NULL END) AS Min2
FROM MeasurementData where sensor_serial_no='4' and month(date_time)='3'
group by data_flag[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2008-04-10 : 07:41:28

dear harsh_athalye,

actually i have mentioned that for data_flag='5' i have 14 rows

and for data_flag='6' i have 14 rows.
so if i use the below query i am getting 196 rows.it is wrong.the result should have contails only 14 rows

[code]
SELECT * FROM
(SELECT a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag ='5' and sensor_serial_no='4' and month(date_time)='3') a,
(SELECT b.Avg_Binaryvalue as Avg2,b.Min_Binaryvalue as Min2 from MeasurementData b where Attribute_Flag ='6' and sensor_serial_no='4' and month(date_time)='3') b

[code]

expected result:
ex:
Avg1 Min1 Avg2 Min2
__________________________

but using the above query i couldn't get. sos that i have used case else. but your query gives only 2 rows so max should not come. so it is not correct.please help me to do this please

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 07:41:59

I don't know why he didn't like the answers he got here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100498

E 12°55'05.25"
N 56°04'39.16"

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2008-04-10 : 08:28:28

dear peso,
i got this query as help which has given by nr.
but it gives 196 rows
actually
i have 14 rows for data_flag =5 and 14 rows for data_flag=6
so the qurey should give 14 rows only bit it didn't.


select *
from
(SELECT sesnsor_serial_no, a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag ='5') a
join (SELECT sesnsor_serial_no, b.Avg_Binaryvalue as Avg2,b.Min_Binaryvalue as Min2 from MeasurementData b where Attribute_Flag ='6') b
on a.sesnsor_serial_no = b.sesnsor_serial_no

or
select *
from
(SELECT sesnsor_serial_no, a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag ='5') a
full outer join
(SELECT sesnsor_serial_no, b.Avg_Binaryvalue as Avg2,b.Min_Binaryvalue as Min2 from MeasurementData b where Attribute_Flag ='6') b
on a.sesnsor_serial_no = b.sesnsor_serial_no

so please help me friend

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2008-04-10 : 09:41:54

please do any one reply me

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 09:45:46

Please read and understand this blog post
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

You do realize that we have no access to your system?
We can never assist you without proper sample data.

E 12°55'05.25"
N 56°04'39.16"

alanhuro
Starting Member

34 Posts

Posted - 2008-04-11 : 09:19:05

Sqlover

I'm not surprise to see you have 196 rows return. The way SQL works is it correlated each row on one column to match the results on the next column. In your case you have 14 rows which mean 14x14 = 196. By doing your way by creating 2 separated data files from FROM stament is similar to using CASE statement. The problem using your way or case is the data can not be group together. Only way I could think of is creating the temp table and copy the data to it. For example the data structure like this

date-------------Var------------result
04/05/08---------A-----------------20
04/05/08---------B-----------------30
04/06/08---------C-----------------80

If doing your way it will return with 9 row
Date---------REsult-Var-A------result-Var-B---------result-Var-C
04/05/08---------20----------------30-----------------Null
04/05/08---------20----------------30-----------------Null
04/05/08---------20----------------30-----------------Null
04/05/08---------20----------------30-----------------Null
04/05/08---------20----------------30-----------------Null
04/05/08---------20----------------30-----------------Null
04/06/08---------Null--------------Null---------------80
04/06/08---------Null--------------Null---------------80
04/06/08---------Null--------------Null---------------80

Only way I know is to create a Temp table and insert the data into so the result should look like

Date---------REsult-Var-A------result-Var-B---------result-Var-C
04/05/08---------20----------------Null-----------------Null
04/05/08---------Null--------------30-----------------Null
04/05/08---------Null--------------Null-----------------80

Once of the problem I posted is similar to your case and I still haven't found any solution yet. I have not got any solution from everyone yet. If you found anything please let me know.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100617

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources