SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Multiple Where Clauses and Count Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sevarg11
Starting Member

USA
1 Posts

Posted - 04/11/2011 :  14:37:09  Show Profile  Reply with Quote
I'm trying to build a reporting app that pulls data based on criteria passed from an .aspx page. The issue that I have is that I need to filter the results based on whether or not their message count is greater than 4, but I also need to pull based on other criteria. Below is the stored procedure that I am using. I know it doesn't work right now and I'm wondering what I should do to get it to work.

ALTER PROCEDURE [dbo].[Get_call_time_sites]
@SiteID int,
@StartDate DateTime,
@EndDate DateTime
AS
BEGIN

SELECT
us.id_site AS SiteID,
us.id_moderator AS moderatorID,
moderator.last_name +', '+moderator.first_name AS moderatorName,
us.start_time AS StartTime,
us.end_time AS EndTime,
datediff(ss, us.start_time, h.created)/60 AS WaitTime,
datediff(ss, us.start_time, (CASE WHEN us.user_activity_check < us.end_time THEN user_activity_check ELSE us.end_time END))/60 AS CallTime

FROM uchat.dbo.History h INNER JOIN uchat.dbo.UserSession us ON h.id_user_session = us.id_user_session
JOIN dbo.Moderator moderator ON us.id_moderator = moderator.id_modetator
JOIN dbo.Message mess ON us.id_user_session = mess.id_user_session

WHERE h.id_user_session = us.id_user_session
AND moderator.id_modetator = us.id_moderator
AND (us.id_site = CASE WHEN @SiteID = 0
THEN us.id_site
ELSE @SiteID END)
AND us.start_time >= @StartDate
AND us.end_time <= @EndDate
AND h.id_history_type = 1
AND COUNT(mess.message) >=4

ORDER BY moderatorID
END

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 04/11/2011 :  14:41:21  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Since you have an aggregation you need a group by. What defines the group that the count is based on. Then the count criteria goes into the having clause

--
Gail Shaw
SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000