Re: Cross tabulations

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 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;

out - in = work_time

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

> "Muhyiddin A.M Hayat" <middink(at)indo(dot)net(dot)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
>