• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 469
  • Last Modified:

sql issue

ALTER PROCEDURE dbo.test
(
@DefaultBegDate datetime,
@DefaultEndDate datetime
)
AS
set nocount on
 
 /*================================================================================ */
/* Begin: Tmp Tables Section */

SELECT Branch_ID, TmpRefID, DateCreated, sAgencyBroker, StatusID,Delete_Ind,Company_ID into #TmpQuotes FROM tblWSBondQuotes INNER JOIN Agency_Company ON tblWSBondQuotes.sAgencyBroker = Agency_Company.Agency_ID WHERE (Agency_Company.Delete_Ind = 0) and (tblWSBondQuotes.DateCreated BETWEEN CONVERT(DATETIME, @DefaultBegDate, 102) AND CONVERT(DATETIME, @DefaultEndDate, 102)) AND (Agency_Company.Delete_Ind = 0)

/* End: Tmp Tables Section */
/*================================================================================ */
SELECT Branch_Name as 'BranchName', Branch_ID as 'BranchId',
/* //////////////////////////////////////////////////////////////////////////////// */
(
SELECT COUNT(#TmpQuotes.TmpRefID)  
FROM #TmpQuotes INNER JOIN
#TmpAgencyBranchMaster ON #TmpQuotes.sAgencyBroker = #TmpAgencyBranchMaster.Agency_ID
WHERE(#TmpQuotes.StatusID = 1) AND (Branch.Branch_ID = #TmpQuotes.Branch_ID)
GROUP BY #TmpAgencyBranchMaster.Branch_ID  
) as 'CommericalBond_ActiveQuote'
 
FROM            
Branch
RETURN
0
Webboy2008
Asked:
Webboy2008
1 Solution
 
jetskij16Commented:
What is the error you are receiving? I would say it is something related to selecting a count and then using the group by.
0
 
Webboy2008Author Commented:
Error Alert:
Subquery return more than 1 value. this is not permitted when the subquery follow -, !=, <, <=....or when the subquery is used as an expression
0
 
ralmadaCommented:
try

SELECT 
	Branch_Name as 'BranchName', 
	Branch_ID as 'BranchId',
	/* //////////////////////////////////////////////////////////////////////////////// */
	(
		SELECT COUNT(#TmpQuotes.TmpRefID)  
		FROM #TmpQuotes INNER JOIN
		#TmpAgencyBranchMaster ON #TmpQuotes.sAgencyBroker = #TmpAgencyBranchMaster.Agency_ID
		WHERE(#TmpQuotes.StatusID = 1) AND (Branch.Branch_ID = #TmpQuotes.Branch_ID)
	) as 'CommericalBond_ActiveQuote'
FROM            
Branch 
RETURN 

Open in new window

0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now