Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006
    Posts
    14

    Unanswered: Total call Date report using increment date

    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

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Is this what you wanted?



    Code:
      1  select trunc(sysdate) - call_date active_life_days,count(*) qty
      2  from calls
      3  group by trunc(sysdate) - call_date
      4* order by trunc(sysdate) - call_date desc
      5  /
    
    ACTIVE_LIFE_DAYS        QTY
    ---------------- ----------
                   7          2
                   5          3
                   4          4
                   1          1
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Oct 2006
    Posts
    14
    Quote Originally Posted by beilstwh
    Is this what you wanted?



    Code:
      1  select trunc(sysdate) - call_date active_life_days,count(*) qty
      2  from calls
      3  group by trunc(sysdate) - call_date
      4* order by trunc(sysdate) - call_date desc
      5  /
    
    ACTIVE_LIFE_DAYS        QTY
    ---------------- ----------
                   7          2
                   5          3
                   4          4
                   1          1
    I hope that this would be useful to me for resolving the issue.

    Thanks a lot.

    Jayesh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •