[clue-tech] hairy sql (postgres) - suggestions welcome

[Dave Price]

I am trying to analyze call records from an asterisk box and have an
almost working query that is pretty ugly - I wonder if anyone has any
suggestions...

I am grabbing a group  of  relevent rows into a temp table for further
analysis that looks like this:

   Column   |           Type           | Modifiers
------------+--------------------------+-----------
 calldate   | timestamp with time zone |
 src        | character varying(80)    |
 dst        | character varying(80)    |
 channel    | character varying(80)    |
 dstchannel | character varying(80)    |
 duration   | integer                  |

... here is the final query (without vraible parameters) that gives
totals by call direction (in or out) and counts by duration for a
range of durations ...

Any alternate strategies are welcome ... I am no SQL expert

..........................................
   Begin;
   create TEMP TABLE dap_test as
   SELECT calldate, src, dst,  channel, dstchannel, duration

      from CDR WHERE

      accountcode LIKE '%rockynet%' AND
      channel LIKE '%869%' AND
      created_datetime BETWEEN '2006-10-01' and '2006-11-01'
         OR
      accountcode LIKE '%rockynet%' AND
      dstchannel LIKE '%869%' AND
      created_datetime BETWEEN '2006-10-01' and '2006-11-01'

      order by uniqueid;

/* accountcode, channel(s) and date(s) should be parameters */

      create TEMP table dap_in as
         select   calldate::date, count(duration) as total ,
                  sum(duration) as total_time
         from dap_test

         where dstchannel like '%869%'  /* this should be a parameter */
         group by calldate::date
         order by calldate::date ;

      create TEMP table dap_out as
         select   calldate::date, count(duration) as total ,
                  sum(duration) as total_time
         from dap_test

         where    channel like '%869%'   /* this should be a parameter */
         group by calldate::date
         order by calldate::date ;

      create TEMP table dap_less_than_10 as
         select   calldate::date,
                  count(calldate::date) as less_than_10
         from dap_test

         where duration < 10
         group by calldate::date
         order by calldate::date;


      create TEMP table dap_ten_to_thirty as
         select   calldate::date,
                  count(calldate::date) as ten_to_thirty
         from dap_test

         where duration >= 10 and duration < 30
         group by calldate::date
         order by calldate::date;


      create TEMP table dap_thirty_to_sixty as
         select   calldate::date,
                  count(calldate::date) as thirty_to_sixty
         from dap_test

         where duration >= 30 and duration <60
         group by calldate::date
         order by calldate::date;


      create TEMP table dap_sixty_to_onetwenty as
         select   calldate::date,
                  count(calldate::date) as sixty_to_onetwenty
         from dap_test

         where duration >=60 and duration < 120
         group by calldate::date
         order by calldate::date;


      create TEMP table dap_more_than_120 as
         select   calldate::date,
                  count(calldate::date) as more_than_120
         from dap_test

         where duration >= 120
         group by calldate::date
         order by calldate::date;

/*     create table dap_report as */
     select
         t.* ,
         i.total as in_calls,
         i.total_time as in_time,

         o.total as out_calls,
         o.total_time as out_time,

         l.less_than_10,

         ten.ten_to_thirty,

         thirty.thirty_to_sixty,

         sixty.sixty_to_onetwenty,

         bigger.more_than_120

         from dap_all t
            left outer join
               dap_in i on t.calldate=i.calldate
                  left outer join
                     dap_out o  on  i.calldate=o.calldate
                        left outer join
                           dap_less_than_10 l on i.calldate=l.calldate
                              left outer join
                                 dap_ten_to_thirty ten on
i.calldate=ten.calldate
                                    left outer join
                                       dap_thirty_to_sixty thirty on
i.calldate=thirty.calldate
                                          left outer join
                                             dap_sixty_to_onetwenty
sixty on i.calldate=sixty.calldate
                                                left outer join
                                                   dap_more_than_120
bigger on i.calldate=bigger.calldate
         ;
/*     copy dap_report to '/tmp/dap_report.csv' CSV; */

     rollback;
....................................

-- 
aloha,
dave