Home » SQL & PL/SQL » SQL & PL/SQL » Total call Date report using increment date
Total call Date report using increment date [message #196963] Mon, 09 October 2006 06:23 Go to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

Hello,

I would like to know a clue about the following report

Churn

This report produces, for the calls received on the last date, how many of them called for the first time on the start date, how many on the start date + 1, start date + 2, etc.
The changes we need are:
We need to show only totals for this report. Currently, there's a row for every customer, but we only care how many of them called x days ago.
For example, the query should return something like this:
Active Life (days) Qty
62 84
63 135
62 211
... ...
1 12453
0 34128

Please find the script for generating tables with sample data

SQL>create table calls
2 as
3 select 441132394629 cli, to_date('02102006','ddmmyyyy') call_date from dual union all
4 select 441132394629, to_date('05102006','ddmmyyyy') from dual union all
5 select 441132533793, to_date('04102006','ddmmyyyy') from dual union all
6 select 441132533793, to_date('05102006','ddmmyyyy') from dual union all
7 select 441142373223, to_date('04102006','ddmmyyyy') from dual union all
8 select 441142373223, to_date('05102006','ddmmyyyy') from dual union all
9 select 441227763301, to_date('02102006','ddmmyyyy') from dual union all
10 select 441227763301, to_date('04102006','ddmmyyyy') from dual union all
11 select 441227763301, to_date('05102006','ddmmyyyy') from dual union all
12 select 441227763301, to_date('08102006','ddmmyyyy') from dual

Your help would be highly appreciated.

Thanks in advance.

Jayesh

Re: Total call Date report using increment date [message #196973 is a reply to message #196963] Mon, 09 October 2006 07:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
select (trunc(sysdate) - dates.day) active_life, count(cli) qty
from (select distinct cli,first_value(call_date) over (partition by cli order by call_date desc) call_date
      from   calls) first_call
    ,(select to_date(:p_date,'dd-mm-yyyy') + level day
      from dual 
      connect by level <= (sysdate - to_date(:p_date,'dd-mm-yyyy'))) dates
where dates.day = first_call.call_date(+)
group by dates.day;
Re: Total call Date report using increment date [message #197030 is a reply to message #196973] Mon, 09 October 2006 11:15 Go to previous message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

Hi,

Thanks a lot. I will test it and get back to you asap.

Jayesh
Previous Topic: Repeated Sequence generation in Table
Next Topic: What file do I have to run to install the package UTL_RECOMP ?
Goto Forum:
  


Current Time: Sun Dec 11 08:16:10 CST 2016

Total time taken to generate the page: 0.07680 seconds