create table voip.cdr_billing (
  id               integer primary key not null default autoincrement,
  cdrid            integer references voip.cdr_tel(id) on delete cascade on update cascade,
  accounttype      integer,       
  accountid        integer,
  datetime         datetime,
  e164prefix       char( 16 ),
  callduration     integer,
  billingduration  integer,
  minuteprice      double,
  callprice        double,
  currencyrate     double
);  

select
(select first g.name as lang from tel.globalprefixgroup g, tel.globalprefix p where
g.id = prefixgroupid and prefix = e164prefix and prefixgroupsetid =  6 ) lang, 
e164prefix as _e164prefix, minuteprice/currencyrate as minuteprice, count(*), 
sum(billingduration)/ 60  as sbd, sum(callprice/currencyrate) as _sum, sum(callduration)/ 60  as scd 
from voip.cdr_billing where datetime between '2004-08-01' and '2004-08-02' and accounttype in ( 2 ,  3 ,  4 ,  8 ) and accountid =  33  
group by _e164prefix, minuteprice
order by  2 

create index voip_cdr_billing_e164prefix on voip.cdr_billing (e164prefix);
create index voip_cdr_billing_accountid_accounttype on voip.cdr_billing (accounttype, accountid);
create index voip_cdr_billing_datetime on voip.cdr_billing (datetime);
create index voip_cdr_billing_minuteprice on voip.cdr_billing (minuteprice);

( Plan [ Total Cost Estimate:  497 . 43028  ] 
  ( WorkTable
    ( Sort
      ( HashGroupBy
        ( NestedLoopsJoin[ TRUE ]
          ( InList )
          ( IndexScan cdr_billing voip_cdr_billing_accountid_accounttype_datetime )
        )
      )
    )
  )
)
)

select
(select g.name as lang from tel.globalprefixgroup g, tel.globalprefix p where
g.id = prefixgroupid and prefix = e164prefix and prefixgroupsetid =  6 ) lang, 
e164prefix as _e164prefix, minuteprice/currencyrate as minuteprice, count(*), 
sum(billingduration)/ 60  as sbd, sum(callprice/currencyrate) as _sum, sum(callduration)/ 60  as scd 
from voip.cdr_billing where datetime between '2004-08-25' and '2004-08-26' and accounttype in ( 2 ,  3 ,  4 ,  8 ) and accountid =  543  
group by _e164prefix, minuteprice
order by  2 

( Plan [ Total Cost Estimate:  16 . 86433  ] 
  ( WorkTable
    ( Sort
      ( HashGroupBy
        ( NestedLoopsJoin[ TRUE ]
          ( InList )
          ( IndexScan cdr_billing voip_cdr_billing_accountid_accounttype_datetime )
        )
      )
    )
  )
)
( SubQ  1  [ Total Cost Estimate: . 0128049703  ] 
  ( NestedLoopsJoin[ TRUE ]
    ( IndexScan ( GlobalPrefix p ) PrefixName )
    ( IndexScan ( GlobalPrefixGroup g ) GlobalPrefixGroup[ g.PrefixGroupSetID =   6  :  3 . 06243673 % Statistics ] )
  )
)

( Plan [ Total Cost Estimate:  19 . 44261881  ] 
  ( WorkTable
    ( Sort
      ( HashGroupBy
        ( Left Outer *HashJoin
          ( HashFilter 
            ( NestedLoopsJoin[ TRUE ]
              ( InList )
              ( IndexScan cdr_billing voip_cdr_billing_accountid_accounttype_datetime )
            )
          )
          ( NestedLoopsJoin[ TRUE ]
            ( IndexScan ( GlobalPrefixGroup g ) GlobalPrefixGroupSet )
            ( IndexScan ( GlobalPrefix p ) GlobalPrefixGroup[ hash( p.Prefix ) in hashmap( cdr_billing.e164prefix ) : . 007117643 % Guess ] )
          )
        )
      )
    )
  )
)

create table "Tel".GlobalPrefixGroup (
	ID                int primary key default autoincrement,
	PrefixGroupSetID  int references "Tel".GlobalPrefixGroupSet(ID) on update cascade on delete cascade,
        Name              char( 64 )
); 

create table "Tel".GlobalPrefix (
	ID                int primary key default autoincrement,
	PrefixGroupID     int references "Tel".GlobalPrefixGroup(ID) on update cascade on delete cascade,
        Prefix            char( 16 )
); 

select
e164prefix as _e164prefix, minuteprice/currencyrate as minuteprice, count(*), 
sum(billingduration)/ 60  as sbd, sum(callprice/currencyrate) as _sum, sum(callduration)/ 60  as scd 
from voip.cdr_billing, 
LATERAL (select g.name as lang from tel.globalprefixgroup g, tel.globalprefix p where g.id = prefixgroupid and prefix = e164prefix and prefixgroupsetid =  6 ) lang
where datetime between '2004-08-25' and '2004-08-26' and accounttype in ( 2 ,  3 ,  4 ,  8 ) and accountid =  33  
group by _e164prefix, minuteprice
order by  2 

( Plan [ Total Cost Estimate:  22 . 06509988  ] 
  ( WorkTable
    ( Sort
      ( HashGroupBy
        ( HashJoin*
          ( NestedLoopsJoin[ TRUE ]
            ( IndexScan ( GlobalPrefixGroup g ) GlobalPrefixGroupSet )
            ( IndexScan ( GlobalPrefix p ) GlobalPrefixGroup )
          )
          ( NestedLoopsJoin[ TRUE ]
            ( InList )
            ( IndexScan cdr_billing voip_cdr_billing_accountid_accounttype_datetime )
          )
        )
      )
    )
  )
)