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.
| Author |
Topic |
|
sevarg11
Starting Member
1 Post |
 Posted - 2011-04-11 : 14:37:09
|
| 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 DateTimeASBEGIN 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 moderatorIDEND |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-11 : 14:41:21
|
| 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 ShawSQL Server MVP |
 |
|
|
|
|
|
|
|
Multiple Where Clauses and Count Statement