help with CASE - 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

help with CASE

Author

Topic

Fakir
Starting Member

2 Posts

Posted - 2007-04-18 : 23:39:49

I am trying to write a query that needs a decision, probably using CASE.

Here is the problem.

There are 2 observation posts, one of which measures water level only, while the other measures water level and water temperature.

My table looks like this:

date, post, data_kind, value
2007/04/19 12:45, A, 1, 999
2007/04/19 12:45, B, 1, 998
2007/04/19 12:45, B, 2, 19
2007/04/19 13:55, A, 1, 996
2007/04/19 14:00, B, 2, 20.5

data_kind 1: water level, 2: temperature

The end result should look like this:

date, A_water_level,B_water_level,B_water_temp
2007/04/19 12:45, 999, 998, 19
2007/04/19 13:55, 996, null, null
2007/04/19 14:00, null, null, 20.5

(I used comma to separate data, so that it might be easier to read(?))

How do I start? Can anyone give me a hint, please?

Thank you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-19 : 00:00:23

[code]
declare @table table
(
date datetime,
post char(1),
data_kind int,
value decimal(5,1)
)

insert into @table
select '2007/04/19 12:45', 'A', 1, 999 union all
select '2007/04/19 12:45', 'B', 1, 998 union all
select '2007/04/19 12:45', 'B', 2, 19 union all
select '2007/04/19 13:55', 'A', 1, 996 union all
select '2007/04/19 14:00', 'B', 2, 20.5

select date,
A_water_level = sum(case when post = 'A' and data_kind = 1 then value end),
B_water_level = sum(case when post = 'B' and data_kind = 1 then value end),
A_water_temp = sum(case when post = 'A' and data_kind = 2 then value end),
B_water_temp = sum(case when post = 'B' and data_kind = 2 then value end)
from @table
group by date

date A_water_level B_water_level A_water_temp B_water_temp
------------------------ ---------------- ---------------- --------------- --------------
2007-04-19 12:45:00.000 999.0 998.0 NULL 19.0
2007-04-19 13:55:00.000 996.0 NULL NULL NULL
2007-04-19 14:00:00.000 NULL NULL NULL 20.5
[/code]

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-19 : 00:14:07

[code]

declare @t table (date datetime, post char(1), data_kind int, value float)

insert into @t
select '2007/04/19 12:45', 'A', 1, 999 union all
select '2007/04/19 12:45', 'B', 1, 998 union all
select '2007/04/19 12:45', 'B', 2, 19 union all
select '2007/04/19 13:55', 'A', 1, 996 union all
select '2007/04/19 14:00', 'B', 2, 20.5

select
date
,max(case when post='A' and data_kind=1 then value end) as A_water_level
,max(case when post='B' and data_kind=1 then value end) as B_water_level
,max(case when post='B' and data_kind=2 then value end) as B_water_temp
from @t
group by date
[/code]

EDIT:

i guess i should refresh the page before posting a long answer like this... how can I compete with you guys anyway. so fast!

www.elsasoft.org

Fakir
Starting Member

2 Posts

Posted - 2007-04-19 : 00:44:53

My God, I don't know how can I thank you guys. Thank you khtan and jezemine, you made my day!
Fakir

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources