Message-ID: <005a01c4ba6f$9830fce0$4f00a8c0@middinks>
From: "Muhyiddin A.M Hayat" <middink@indo.net.id>
To: "Greg Stark" <gsstark@mit.edu>
Cc: <pgsql-sql@postgresql.org>
References: <012501c4b65f$9b70b620$4f00a8c0@middinks>
	<878ya128ya.fsf@stark.xeocode.com>
Subject: Re: Cross tabulations
Date: Mon, 25 Oct 2004 16:49:51 +0800
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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