From: Muhyiddin A.M Hayat <middink@indo.net.id>
To: Greg Stark <gsstark@mit.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: Cross tabulations
Date: Mon, 25 Oct 2004 16:49:51 +0800
Message-ID: <005a01c4ba6f$9830fce0$4f00a8c0@middinks> (raw)
References: <012501c4b65f$9b70b620$4f00a8c0@middinks>
<878ya128ya.fsf@stark.xeocode.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: middink@indo.net.id, gsstark@mit.edu
Subject: Re: Cross tabulations
In-Reply-To: <005a01c4ba6f$9830fce0$4f00a8c0@middinks>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox