T-SQL question

[JackV]

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

[Mongo]

Because there are a couple different tacts that can be
made when negative report listings (no calls, no letters)
are to be included, I need to ask:

1. Is your report to include a listing for all agents,
i.e., AgentId 101 had 0 calls and 0 letters on Monday?

2. Is your report to include similar zero listings for
all agents for all dates between startdate and enddate
when, say, the office was closed and no agents worked?

3. Does your database have a tblAgent table that uniquely
defines your AgentIds? If so, does it include or join to
another table that identifies each agent's effective and
term dates?

4. Does tblCustEvents.CallDate include or exclude the
time of day that the call was made?

[Edited by Mongo on 06-14-2000 at 08:06 PM]

[JackV]

1. No, if no calls for that agent was made then don't list them.

2. No

3. Actually no, my use of number values was misleading. The actual values are like brians, maryr, ...etc. I believe that the value is typed into this table from a front end by the agent(user) when they log each call or letter handled.

4. Calldate is just a short date value. There is a separate field for starttime and endtime in the tblCustEvents table.

I've gotten a little closer with what I want. I'm now working with the following statement

CREATE PROCEDURE sp_rep_total_daily
@startdate smalldatetime, @enddate smalldatetime
AS
SELECT [tblcustevents].[CallDate], [tblcustevents].[AgentId], Count([tblcustevents].[CallType]) AS [Number of Calls], [tblcustevents].[CallType]
FROM tblcustevents
WHERE CallDate BETWEEN @startdate AND @enddate
GROUP BY [tblcustevents].[CallDate], [tblcustevents].[AgentId], [tblcustevents].[CallType]
HAVING (((tblcustevents.CallType)="call")) OR (((tblcustevents.CallType)="letter"))
ORDER BY [tblcustevents].[CallDate], [tblcustevents].[AgentId];

This gets me the sum totals for each calltype per day for each agent. I then have Crystal group the dates by day and also group by Agent. Only problem with this is that it's displayed vertically on the report, but it's better than what I had before.

-Jack

[Mongo]

I play in Sybase where life is different; so this is an academic exercise for me.
I apologize if it doesn't work, but I couldn't test it. Though I'd be interested to know if it's good code or not.
Hope it helps

-- if these don't exist, make them 
--   CREATE INDEX [index_name] ON tblCustEvents(CallDate, AgentID)
--   CREATE INDEX [index_name] ON tblCustEvents(CallDate, AgentID, CallType)

CREATE TABLE tblEventAgents
( CallDate  smalldatetime                              NOT NULL 
, AgentID   [same datatype as tblCustEvents.AgentID]   NOT NULL )
CREATE UNIQUE INDEX [index_name] ON tblEventAgents( CallDate, AgentID )

CREATE PROC usp_rep_daily_total_report 
@startdate smalldatetime, @enddate smalldatetime 
AS 
DECLARE @t_dt smalldatetime
  TRUNCATE TABLE tblEventAgents
  -- since it happens (and hint to handle nulls prior to call)
  IF ( @enddate < @startdate )
  BEGIN
    SELECT @t_dt =  @startdate
    SELECT @startdate =  @enddate
    SELECT @enddate =  @t_dt
  END
  -- between bias 
  SELECT @startdate = ( SELECT DATEADD( 'day', -1, @startdate ))
  SELECT @enddate =   ( SELECT DATEADD( 'day', 1, @enddate ))
  -- set supporting table
  --   Note: if CallType can only be 'Call' or 'Letter', drop IN part of the SARG
  INSERT tblEventAgents( CallDate, AgentID )
    SELECT a.CallDate, a.AgentID 
      FROM tblCustEvents AS a
      WHERE a.CallDate > @startdate 
        AND a.CallDate < @enddate 
        AND a.AgentID > ''
        AND a.CallType IN ('Call', 'Letter')
      GROUP BY a.AgentID, a.CallDate
  -- fruit of your labor...
  -- Would it make better sense to insert into table for sort, etc.?  Your call.
  SELECT a.AgentID, a.CallDate
    , ( SELECT COUNT(*) 
          FROM tblCustEvents AS b
          WHERE b.CallDate = a.CallDate
            AND b.AgentID = a.AgentID 
            AND b.CallType = 'Call' ) AS NumberOfPhoneCalls
    , ( SELECT COUNT(*) 
          FROM tblCustEvents AS c
          WHERE c.CallDate = a.CallDate
            AND c.AgentID = a.AgentID 
            AND c.CallType = 'Letter') AS NumberOfLetters 
    FROM tblEventAgents AS a;

[JackV]

I get an error at the following lines:

SELECT @startdate = ( SELECT DATEADD( 'day', -1 @startdate ))
SELECT @enddate = ( SELECT DATEADD( 'day', 1, @enddate ))

Tells me that I have an invalid parameter for dateadd for both lines.

-Jack

[JHausmann]

Try it without the apostrophe's:

SELECT @startdate = ( SELECT DATEADD(day, -1 @startdate ))

[JackV]

Thanks alot there Mongo and you to JHausman.

It works like a charm!!!

-Jack Vinitsky

[JackV]

It must be Friday. If I try to requery the report in Crystal, I get an ODBC Error: Fractional Truncation.

Everything works fine in the SQL 7 query analyzer but not in Crystal. Looks like ODBC is having a problem with the truncation.

Any one have any ideas?

--Jack

[Mongo]

Sounds like an even better reason to have your sp's final query insert into a result table, i.e., then have Crystal draw straight from that table, yes? *S*

[JackV]

You're probably right...

But I replaced TRUNCATE TABLE tblEventAgents with DELETE FROM tblEventAgents and it seems to work fine

Guess ODBC doesn't like Truncate.

I connected an ACCESS front end to the database and I ran the sp through a SQL Pass Through Query and worked there as well. Oh well. Much thanks again

Regards,
Jack Vinitsky