How to count only the last calls made to each customer? 1

[johnwolf]

Using CR8 with Oracle

I am currently counting calls to customers in our call center, using a table which is as following

CALLS_TBL

CALL_ID
AGENT_ID
CUSTOMER_ID
CALL_TYPE (PROSPECT, NEGOTIATE, SELL...)
CALL_RESULT (UNAVALAIBLE, QUALIFIED, SALE,...)
CALL_STATUS (COMPLETED, PENDING)
CALL_DATE

On a given period established with parameters and record selection formula.

Groups are the following:
Group#1 AGENT_ID
Group#2 CALL_TYPE
Group#3 CALL_RESULT

To count customers called I used the following formulas


@CUSTOMERIDCALLS
If {CALLS_TBL.CALL_STATUS}='COMPLETED' then {CALLS_TBL.CUSTOMER_ID} else {@null}


Then I use
@CUSTOMERSCALLEDBYRESULT
DistinctCount({@CUSTOMERIDCALLS},{CALLS_TBL.CALL_RESULT})

@CUSTOMERSCALLEDBYCALLTYPE
DistinctCount({@CUSTOMERIDCALLS},{CALLS_TBL.CALL_TYPE})

@CUSTOMERSCALLEDBYAGENT
DistinctCount({@CUSTOMERIDCALLS},{CALLS_TBL.AGENT_ID})


So far so good.


So with a set of data like

CUSTOMER_ID CALL_ID DATE CALL_TYPE CALL RESULT
1 100 11112008 P UNAVAILABLE
1 165 17112008 P UNAVAILABLE
1 236 19112008 P QUALIFIED
2 126 12112008 P QUALIFIED
3 137 13112008 P UNAVAILABLE

The report looks like

GH#1 COUNT
AGENT 1 3 //@CUSTOMERSCALLEDBYAGENT

GH#2
P (PROSPECTION) 3 //@CUSTOMERSCALLEDBYCALLTYPE

GH#3
UNAVAILABLE 2 //@CUSTOMERSCALLEDBYRESULT
QUALIFIED 2 //@CUSTOMERSCALLEDBYRESULT

What I would like to count is only the latest CALL_RESULT in GH#3

so that it looks like

GH#3
UNAVAILABLE 1
QUALIFIED 2

Any suggestion welcome, do not hesitate to ask for more details if I am not clear enough

 

[johnwolf]

I did it and time was less than 1 minute. That's great.

I suppose I cannot use parameters in the SQL expression and I have to write dates.

 

[lbass]

Yes, you cannot use parameters in SQL expressions. SInce you are using CR8, the only way you could build in parameters would be to replace the SQL expression with subreports that do the equivalent, and then you would have to use shared variables for all of your calculations.

-LB