Nested Select and Join in Dynamics CRM

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)

Nested Select and Join in Dynamics CRM

Author

Topic

Chad_C
Starting Member

7 Posts

Posted - 2010-03-04 : 15:39:01

I'm trying to perform a nasty nested select statement in Dynamics CRM in order to achieve the following output

[username | Won | Lost]

where "Won" and "Lost" are the results of a count operation.

There are two tables involved due to the structure of Dynamics CRM (with customizable pick lists), which are "Opportunity" and "StringMap". StringMap.value can be a number of different values, including but not limited to "Lost" and "Won".

The following two queries return the results I need, but I need to combine them into one query.

Query #1:

select owneridname, count(sm.value) as 'Lost'
from Opportunity
join StringMap as sm on (Opportunity.statuscode = sm.AttributeValue)
where
(Opportunity.Department = '3' and
sm.Value = 'Lost')
Group by owneridname

Query #2:

select owneridname, count(sm.value) as 'Won'
from Opportunity
join StringMap as sm on (Opportunity.statuscode = sm.AttributeValue)
where
Opportunity.Department = '3' and
sm.Value = 'Won'
Group by owneridname

Oh SQL gods, how do I combine these two and get a table which shows username (owneridname), "Won" count, and "Lost" count?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-04 : 16:56:38

This?

select owneridname
,sum(case when sm.value='Lost' then 1 else 0 end) as 'Lost'
,sum(case when sm.value='Won' then 1 else 0 end) as 'Won'
from Opportunity o
inner join StringMap sm on o.statuscode = sm.AttributeValue
where o.Department = '3' and sm.Value in ('Lost','Won')
Group by owneridname

Chad_C
Starting Member

7 Posts

Posted - 2010-03-04 : 16:57:59

quote:

Originally posted by vijayisonly

This?

select owneridname
,sum(case when sm.value='Lost' then 1 else 0 end) as 'Lost'
,sum(case when sm.value='Won' then 1 else 0 end) as 'Won'
from Opportunity o
inner join StringMap sm on o.statuscode = sm.AttributeValue
where o.Department = '3' and sm.Value in ('Lost','Won')
Group by owneridname

That was quick -- and accurate! Thank you so much. I have a lot to learn :)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-04 : 17:02:37

Np. You're welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources