Re: Cross tabulations - Mailing list pgsql-sql

Dear,

Thanks, that query is work, so.
So, i would like to calculate total work time




select date, employee_id,      (select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'In') as in,      (select check_time-check_time::date from test
whereemployee_id =
 
x.employee_id and check_time::date = date and state = 'Break Out') as
break_out,      (select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'Break In') as
break_in,      (select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'Out') as out from (select distinct employee_id, check_time::date
asdate from test) as
 
x;


out - in = work_time






----- Original Message ----- 
From: "Greg Stark" <gsstark@mit.edu>
To: "Muhyiddin A.M Hayat" <middink@indo.net.id>
Cc: <pgsql-sql@postgresql.org>
Sent: Wednesday, October 20, 2004 2:35 PM
Subject: Re: [SQL] Cross tabulations


> "Muhyiddin A.M Hayat" <middink@indo.net.id> writes:
>
> > Dear all,
> >
> > I need to do something similar to a cross tabulation, but without any
> > aggregation.
>
> join your table to itself four times:
>
> select *
>   from (select check_time::date as date, employee_id,
check_time-check_time::date as in from test where state = 'In') as a
>   join (select check_time::date as date, employee_id,
check_time-check_time::date as break_out from test where state = 'Break
Out') as b using (employee_id,date)
>   join (select check_time::date as date, employee_id,
check_time-check_time::date as break_in from test where state = 'Break In')
as d using (employee_id,date)
>   join (select check_time::date as date, employee_id,
check_time-check_time::date as out from test where state = 'Out') as e using
(employee_id,date) ;
>
> Note that this will do strange things if you don't have precisely four
records
> for each employee.
>
> Alternatively use subqueries:
>
> select date, employee_id,
>        (select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'In') as in,
>        (select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'Break Out') as
break_out,
>        (select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'Break In') as
break_in,
>        (select check_time-check_time::date from test where employee_id =
x.employee_id and check_time::date = date and state = 'Out') as out
>   from (select distinct employee_id, check_time::date as date from test)
as x;
>
> This will at least behave fine if there are missing records and will give
an
> error if there are multiple records instead of doing strange things.
>
> Neither of these will be particularly pretty on the performance front.
>
> -- 
> greg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>