Multiple SQL Select Statements in a Stored Procedure & joining them

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 2000 Forums

Transact-SQL (2000)

Multiple SQL Select Statements in a Stored Procedure & joining them

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-08-25 : 08:57:35

ling writes "I have 3 Select Statements in my Stored Procedure.
I would like to Join the three into one SQL Select to get the end results as one Query. I've written the three Select Statements in the Stored Proc but do not know how to write the last statement that joins all of them. The Key used to join the three is AgentID. If they're 3 separate tables or views, then I would know how to join them. But in the Stored Proc, I'm not sure whether it can be done or not.
I've attached the Stored Proc below:

CREATE PROCEDURE CbcSpCombineCBC
@Quarter int
AS

BEGIN
SELECT Agent.AgentID, Agent.Name, Agent.Source, Agent.Add1, Agent.Add2,
Agent.Add3, Agent.Postcode, Agent.Town, Agent.PiamReg,
COUNT(CNote.CnNo) AS BreachedCn, CNote.NoOfDays
FROM Agent INNER JOIN
CNote ON Agent.AccNo = CNote.AccNo
GROUP BY Agent.Name, Agent.Source, Agent.Add1, Agent.Add2,
Agent.Add3, Agent.Postcode, Agent.Town, Agent.PiamReg,
CNote.NoOfDays, Agent.AgentID, DATEPART(quarter, CNote.CnIssueDt)
HAVING DATEPART(quarter, CNote.CnIssueDt) = @Quarter

SELECT Agent.AgentID, Agent.Name, Agent.Source, Agent.Add1, Agent.Add2,
Agent.Add3, Agent.Postcode, Agent.Town, Agent.PiamReg,
COUNT(CNote.CnNo) AS NoOfCn, CNote.NoOfDays,
DATEPART(quarter, CNote.CnIssueDt)
AS qtr
FROM Agent INNER JOIN
CNote ON Agent.AccNo = CNote.AccNo
GROUP BY Agent.Name, Agent.Source, Agent.Add1, Agent.Add2,
Agent.Add3, Agent.Postcode, Agent.Town, Agent.PiamReg,
CNote.NoOfDays, Agent.AgentID, DATEPART(quarter, CNote.CnIssueDt)
HAVING (CNote.NoOfDays > 0) AND DATEPART(quarter, CNote.CnIssueDt) = @Quarter

SELECT Agent.AgentID, MIN(CNote.NoOfDays) AS MinDays,
MAX(CNote.NoOfDays) AS MaxDays, CNote.NoOfDays
FROM Agent INNER JOIN
CNote ON Agent.AccNo = CNote.AccNo
GROUP BY Agent.AgentID, CNote.NoOfDays, DATEPART(quarter, CNote.CnIssueDt)
HAVING (CNote.NoOfDays > 0) AND DATEPART(quarter, CNote.CnIssueDt) = @Quarter

END"

Nazim
A custom title

1408 Posts

Posted - 2003-08-25 : 11:40:59

The most easiest way would be . something on this lines.

CREATE PROCEDURE CbcSpCombineCBC
@Quarter int
AS

BEGIN

Select a.*,b.*,c.*

from
(
SELECT Agent.AgentID, Agent.Name, Agent.Source, Agent.Add1, Agent.Add2,
Agent.Add3, Agent.Postcode, Agent.Town, Agent.PiamReg,
COUNT(CNote.CnNo) AS BreachedCn, CNote.NoOfDays
FROM Agent INNER JOIN
CNote ON Agent.AccNo = CNote.AccNo
GROUP BY Agent.Name, Agent.Source, Agent.Add1, Agent.Add2,
Agent.Add3, Agent.Postcode, Agent.Town, Agent.PiamReg,
CNote.NoOfDays, Agent.AgentID, DATEPART(quarter, CNote.CnIssueDt)
HAVING DATEPART(quarter, CNote.CnIssueDt) = @Quarter
) a
inner join
(
SELECT Agent.AgentID,
COUNT(CNote.CnNo) AS NoOfCn, CNote.NoOfDays,
DATEPART(quarter, CNote.CnIssueDt)
AS qtr
FROM Agent INNER JOIN
CNote ON Agent.AccNo = CNote.AccNo
GROUP BY Agent.Name, Agent.Source, Agent.Add1, Agent.Add2,
Agent.Add3, Agent.Postcode, Agent.Town, Agent.PiamReg,
CNote.NoOfDays, Agent.AgentID, DATEPART(quarter, CNote.CnIssueDt)
HAVING (CNote.NoOfDays > 0) AND DATEPART(quarter, CNote.CnIssueDt) = @Quarter
) b
on a.AgentID=b.AgentID
inner join
(

SELECT Agent.AgentID, MIN(CNote.NoOfDays) AS MinDays,
MAX(CNote.NoOfDays) AS MaxDays, CNote.NoOfDays
FROM Agent INNER JOIN
CNote ON Agent.AccNo = CNote.AccNo
GROUP BY Agent.AgentID, CNote.NoOfDays, DATEPART(quarter, CNote.CnIssueDt)
HAVING (CNote.NoOfDays > 0) AND DATEPART(quarter, CNote.CnIssueDt) = @Quarter ) c
on b.Agentid=c.AgentiD

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources