Hi I need a little help with creating a stored procedure. This proc is going to be a datasource for a report in Crystal Reports 7.

There are 3 fields in question. AgentId, Calldate, and CallType. There are 2 possible values for CallType which are "phone" & "letter".

The report is supposed to generate a daily total for both types of calls for each AgentID based on a date range supplied by the user. Thus the report is going to look like this:

Agent Total Calls Total Letters
100 34 5

I've been able to set variables (in the stored procedure) equal to the total amounts of "phone" values and "letter" values for calltype but I can't seem to divide them by day nor agentid.

This is what I've come up with so far:

Create Procedure sp_rep_daily_total_report
@startdate smalldatetime, @enddate smalldatetime
AS
Declare @NumberOfPhoneCalls int, @NumberOfLetters int

SELECT @NumberOfPhoneCalls = COUNT(CallType)FROM tblCustEvents
WHERE CallType = 'Call' AND CallDate Between @startdate and @enddate

SELECT @NumberOfLetters = COUNT(CallType) FROM tblCustEvents
WHERE CallType = 'Letter' AND CallDate Between @startdate and @enddate

SELECT AgentId, CallDate, @NumberOfPhoneCalls AS NumberOfPhoneCalls, @NumberOfLetters AS NumberOfLetters
FROM tblCustEvents
WHERE CallDate Between @startdate and @enddate
GO


Thanks in advance for any help,

Jack Vinitsky