SQL JOIN 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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL JOIN HELP!!!

Author

Topic

OKsoccer24
Starting Member

8 Posts

Posted - 2009-07-24 : 16:49:53

select application, infodel_environment, avg(duration) as duration07
from events
where year_month = '200907'
group by application, infodel_environment

select application, infodel_environment, avg(duration) as duration06
from events
where year_month = '200906'
group by application, infodel_environment

Here are two sql statements that return the avg duration for a given month for an application in that environment.
The final result I need is
Application Environment Duration07 Duration06
xxxxxxxxxxx xxxxxxxxx xxx xxx

Is it even possible to do such an act?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-24 : 16:58:06

[code]SELECT
application,
infodel_environment,
MAX(duration06) AS duration06,
MAX(duration07) AS duration07
FROM
(
select application, infodel_environment, NULL as duration06, avg(duration) as duration07
from events
where year_month = '200907'
group by application, infodel_environment

UNION ALL

select application, infodel_environment, avg(duration) , NULL
from events
where year_month = '200906'
group by application, infodel_environment
) AS T
GROUP BY
application,
infodel_environment
[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-24 : 17:00:13

Or possibly,

select 
	application, 
	infodel_environment, 
	AVG(CASE WHEN year_month = '200906' THEN duration ELSE NULL END) AS duration06,
	AVG(CASE WHEN year_month = '200907' THEN duration ELSE NULL END) AS duration07
from events
where year_month IN ('200906', '200907')
group by application, infodel_environment

OKsoccer24
Starting Member

8 Posts

Posted - 2009-07-24 : 17:14:16

That worked great! Thanks

Would there be anyway to add year_month 200905 to it?

Making the final table

Application Environemnt Duration07 Duration06 Duration 05
xxxxxxx xxxxxx xxx xxx xxx

OKsoccer24
Starting Member

8 Posts

Posted - 2009-07-24 : 17:18:56

Never mind, The second solution made it really easy to add more months.

Thank you for your fast responses!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources