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

SQL Server Development (2000)

Query Help?

Author

Topic

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-25 : 10:26:29

Hi,
I am using Two Tables.AddressDetails and Calldetails.AddressBook consists of BID,Branch,Name,Address,City,State.CallDetails consists of SrNo,Branch,Place,Model,Status.Now I want my output based on the branch id passed as parameter from my stored Procedure.Somehow I could not generate the output.Following is my Code.


ALTER      Procedure amcsp_GetPendingDetails
(
  @intBid as int = 0,
  @intSrNo as int = 0	 
)

as
	
	BEGIN

            DECLARE @Branch AS VARCHAR(40)
	    SELECT  @Branch = Branch FROM ADDRESSDETAILS    
	   			
	if(@intSrNo = Null ) or (@intSrNo = 0)	        

	   BEGIN
		
		SELECT BRANCH,COUNT(DISTINCT BRANCH), SUM(CASE WHEN NOOFDAYSLOS BETWEEN 7 AND 30 THEN 1 ELSE 0 END)'Over a week',
		FROM CALLDETAILS 	
		WHERE STATUS = 'P' and Branch = @Branch 
		GROUP BY BRANCH
		ORDER BY BRANCH
		

	   END

END

Plz Hlp me...
Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-25 : 12:18:25

You can do this in a single SELECT with a join, you don't need to use a SELECT to get the branch first.

But you have a whole bunch of problems here.
1. You are not using @intBid in your first query to get one branch. 2. What is NOOFDAYSLOS, you didn't list it as one of the columns in your CallDetails table, but you are trying to use it in the main query.
3. Your design is bad - you should have the BID column in the CallDetails table not the Branch.

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-26 : 01:51:54

Ok,
After including BID in my calldetails table How Should be query be Like. Plz Explain....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-26 : 02:08:32

Something like this

ALTER Procedure amcsp_GetPendingDetails
(
	@intBid int = 0,
	@intSrNo int = 0	 
)
as

set nocount on
	   			
if @intSrNo IS Null or @intSrNo = 0
	SELECT		BRANCH,
			COUNT(DISTINCT BRANCH),
			SUM(CASE WHEN NOOFDAYSLOS BETWEEN 7 AND 30 THEN 1 ELSE 0 END) 'Over a week',
	FROM		CALLDETAILS
	INNER JOIN	ADDRESSDETAILS ON ADDRESSDETAILS.Branch = CALLDETAILS.Branch
	WHERE		STATUS = 'P'
	GROUP BY	BRANCH
	ORDER BY	BRANCH

but it makes no sense. What branch do you like to return? Pu another WHERE clause.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources