Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

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

Starting Member

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

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
AND us.start_time >= @StartDate
AND us.end_time <= @EndDate
AND h.id_history_type = 1
AND COUNT(mess.message) >=4

ORDER BY moderatorID

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  
 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.07 seconds. Powered By: Snitz Forums 2000