|
sevarg11
Starting Member
USA
1 Posts |
 Posted - 04/11/2011 : 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 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 |
|
Multiple Where Clauses and Count Statement
