Multiple columns using subqueries? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Multiple columns using subqueries?

Author

Topic

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2008-02-14 : 17:41:45

Hey guys, I have a query that currently pulls data from a MS CRM database:
SELECT New_CampaignIdName 'OneMonthCampaign_Name', sum(case when Lead.new_vendorCreateDate >= dateadd(hh, 7, @1MoStartDate) and lead.new_vendorCreateDate < dateadd(hh, 7, @1MoEndDate) AND lead.salesstagecode IN (1,2,3,4,5,6,8) then 1 else 0 end) as 'OneMonthValidLeads'
FROM New_CampaignSource
INNER JOIN Lead ON Lead.CampaignId = New_CampaignSource.New_CampaignId
AND New_CampaignSource.New_SourceIdName = 'BNR'
AND New_CampaignSource.StateCode = 0
AND New_CampaignSource.DeletionStateCode = 0
AND lead.salesstagecode IN (1,2,3,4,5,6,8,9)
INNER JOIN SystemUser ON Lead.OwnerId = SystemUser.SystemUserId
INNER JOIN new_degreeprogram ON Lead.new_degreeprogramId = new_degreeprogram.new_degreeprogramId
AND new_degreeprogram.new_degreeCollege IN (2,4)
LEFT JOIN StringMap ON Lead.SalesStageCode = StringMap.AttributeValue
AND StringMap.AttributeName = 'SalesStageCode'
AND StringMap.ObjectTypeCode = 4
WHERE lead.new_vendorcreatedate >= dateadd(hh, 7, @6MoStartDate)
AND lead.new_vendorcreatedate < dateadd(hh, 7, DATEADD(dd,1,CAST(@6MoEndDate AS VARCHAR(11))))
)
)
GROUP BY New_CampaignIdName
ORDER BY New_CampaignIdName

I also have another query that pulls very similar data but for different parameters:

SELECT New_CampaignIdName 'SixMonthsCampaign_Name', sum(case when lead.salesstagecode IN (1,2,3,4,5,6,8) then 1 else 0 end) as 'SixMonthsValidLeads'
FROM New_CampaignSource
INNER JOIN Lead ON Lead.CampaignId = New_CampaignSource.New_CampaignId
AND New_CampaignSource.New_SourceIdName = 'BNR'
AND New_CampaignSource.StateCode = 0
AND New_CampaignSource.DeletionStateCode = 0
AND lead.salesstagecode IN (1,2,3,4,5,6,8,9)

INNER JOIN SystemUser ON Lead.OwnerId = SystemUser.SystemUserId
/* Join on DegreeProgram to query by the college the degree is in (e.g. Col. of Bus.) */
INNER JOIN new_degreeprogram ON Lead.new_degreeprogramId = new_degreeprogram.new_degreeprogramId
AND new_degreeprogram.new_degreeCollege IN (2,4) --Business
/* StringMap contains the SalesStageCode ("Lead Status") picklist values */
LEFT JOIN StringMap ON Lead.SalesStageCode = StringMap.AttributeValue
AND StringMap.AttributeName = 'SalesStageCode'
AND StringMap.ObjectTypeCode = 4
WHERE Lead.DeletionStateCode = 0
AND lead.new_vendorcreatedate >= dateadd(hh, 7, @6MoStartDate)
AND lead.new_vendorcreatedate < dateadd(hh, 7, DATEADD(dd,1,CAST(@6MoEndDate AS VARCHAR(11))))
)
)
GROUP BY New_CampaignIdName
ORDER BY New_CampaignIdName

Both queries will always return the same number of rows, the first columns always matching:

Query 1 Query 2

Col 1 Col 2 Col 1 Col 2
A 1a A 3b
B 0a B 4b
C 7a C 7b
D 4a D 5b

What I'd like to do is somehow be able to combine these two queries into one and show three columns instead:

Col 1 Col 2 Col 3
A 1a 3b
B 0a 4b
C 7a 7b
D 4a 5b

I have tried using the entire second query as a subquery in the select statement of the first query, but SQL Server tells me this:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

I'm not sure how to go about this... It would seem that this isn't too difficult to achieve - I just don't know how to do it. I'd really appreciate your help.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-02-14 : 18:56:01

You can treat each of these queries as if it was a data table and then JOIN the two tables:

select a.Col1, a.Col2, b.Col3
from
(
select <big honking query #1>
) a
inner join
(
select <big honking query #2>
) b
on a.Col1 = b.Col1

You can look up the term "derived table" for further info.

=======================================
Society is like a stew. If you don't keep it stirred up you get a lot of scum on the top. -Edward Abbey, naturalist and author (1927-1989)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-15 : 05:17:46

or simply this will do i guess:-

SELECT New_CampaignIdName 'OneMonthCampaign_Name', sum(case when Lead.new_vendorCreateDate >= dateadd(hh, 7, @1MoStartDate) and lead.new_vendorCreateDate < dateadd(hh, 7, @1MoEndDate) AND lead.salesstagecode IN (1,2,3,4,5,6,8) then 1 else 0 end) as 'OneMonthValidLeads',sum(case when lead.salesstagecode IN (1,2,3,4,5,6,8) and Lead.DeletionStateCode = 0
 then 1 else 0 end) as 'SixMonthsValidLeads'
FROM New_CampaignSource
INNER JOIN Lead ON Lead.CampaignId = New_CampaignSource.New_CampaignId
AND New_CampaignSource.New_SourceIdName = 'BNR' 
AND New_CampaignSource.StateCode = 0 
AND New_CampaignSource.DeletionStateCode = 0
AND lead.salesstagecode IN (1,2,3,4,5,6,8,9)
INNER JOIN SystemUser ON Lead.OwnerId = SystemUser.SystemUserId
INNER JOIN new_degreeprogram ON Lead.new_degreeprogramId = new_degreeprogram.new_degreeprogramId
AND new_degreeprogram.new_degreeCollege IN (2,4)
LEFT JOIN StringMap ON Lead.SalesStageCode = StringMap.AttributeValue
AND StringMap.AttributeName = 'SalesStageCode'
AND StringMap.ObjectTypeCode = 4
WHERE lead.new_vendorcreatedate >= dateadd(hh, 7, @6MoStartDate)
AND lead.new_vendorcreatedate < dateadd(hh, 7, DATEADD(dd,1,CAST(@6MoEndDate AS VARCHAR(11))))
)
)
GROUP BY New_CampaignIdName
ORDER BY New_CampaignIdName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources