drop table sodcafe;

create table sodcafe
(
  cia         char(3) not null,
  empleado    varchar2(15) not null,
  entrada     date not null,
  salida      date,
  constraint sodcafe_pk 
     primary key(cia, empleado, entrada)
);

create index sodcafe_entrada_idx on sodcafe(cia, entrada);

create or replace view sodcafe_det as
   select cia, empleado, entrada, salida, (salida-entrada)*24 minutos
   from sodcafe;

create or replace view sodcafe_res as
   select cia, empleado, last_day(trunc(entrada)) entrada, null salida, sum((salida-entrada)*24) minutos
   from sodcafe
   group by cia, empleado, last_day(trunc(entrada));

create or replace view sodcafe_exc as
   select cia, empleado, last_day(trunc(entrada)) entrada, null salida, sum((salida-entrada)*24-15) minutos
   from sodcafe
   where (salida-entrada)*1440>15
   group by cia, empleado, last_day(trunc(entrada));

