From middink@indo.net.id Wed Oct  20 04:45:47 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 3F6B632ABC7
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 20 Oct 2004 05:45:47 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 80512-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 20 Oct 2004 04:45:43 +0000 (GMT)
Received: from smtp-1.indo.net.id (smtp-1.indo.net.id [202.159.32.98])
	by svr1.postgresql.org (Postfix) with SMTP id F3A3E329F1F
	for <pgsql-sql@postgresql.org>; Wed, 20 Oct 2004 05:45:32 +0100 (BST)
Received: (qmail 25322 invoked from network); 20 Oct 2004 11:45:26 +0700
Received: from smtp.indo.net.id (202.159.32.81)
	by smtp-1.indo.net.id with SMTP; 20 Oct 2004 11:45:26 +0700
Received: (qmail 14656 invoked from network); 20 Oct 2004 11:45:24 +0700
Received: from unknown (HELO middinks) (202.159.122.116)
	by smtp.indo.net.id with SMTP; 20 Oct 2004 11:45:24 +0700
Message-ID: <012501c4b65f$9b70b620$4f00a8c0@middinks>
From: "Muhyiddin A.M Hayat" <middink@indo.net.id>
To: <pgsql-sql@postgresql.org>
Subject: Cross tabulations
Date: Wed, 20 Oct 2004 12:43:00 +0800
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_011C_01C4B6A2.551C1F00"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=HTML_40_50,
	HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200410/138
X-Sequence-Number: 19241

This is a multi-part message in MIME format.

------=_NextPart_000_011C_01C4B6A2.551C1F00
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Dear all,

I need to do something similar to a cross tabulation, but without any
aggregation.

I have below table

 id | employee_id |   state   |     check_time
----+-------------+-----------+--------------------
 21 |           1 | In        | 2004-10-12 21:37:13
 22 |           1 | Break Out | 2004-10-12 21:37:31
 23 |           1 | Break In  | 2004-10-12 21:37:42
 24 |           1 | Out       | 2004-10-12 21:37:50
 25 |           1 | In        | 2004-10-13 19:20:36
 26 |           1 | In        | 2004-10-14 01:33:48
 27 |           1 | Break Out | 2004-10-14 01:59:15
 28 |           1 | Break In  | 2004-10-14 03:15:45
 29 |           1 | Out       | 2004-10-14 03:17:23
 30 |           3 | In        | 2004-10-14 03:17:43
 31 |           3 | Break Out | 2004-10-14 19:32:34
 32 |           2 | In        | 2004-10-14 20:34:15
 33 |           3 | In        | 2004-10-15 02:01:28
 34 |           3 | Break Out | 2004-10-15 02:02:07
 35 |           3 | In        | 2004-10-16 02:06:43
 36 |           1 | In        | 2004-10-16 02:07:33
 37 |           1 | Break Out | 2004-10-16 02:09:09
 38 |           1 | Break In  | 2004-10-16 04:10:21
 39 |           1 | Out       | 2004-10-16 04:12:27
 40 |           3 | Break Out | 2004-10-16 21:38:22


I need something like this:

   date    | employee_id |    in    | break_out | break_id |   out
-----------+-------------+----------+-----------+----------+----------
2004-10-12 |           1 | 21:37:13 | 21:37:31  | 21:37:42 |21:37:50
2004-10-14 |           1 | 01:33:48 | 01:59:15  | 03:15:45 |03:17:23
2004-10-14 |           3 | 03:17:43 | 19:32:34  | 03:15:45 |03:17:23=20=20=
=20
........
........
........






------=_NextPart_000_011C_01C4B6A2.551C1F00
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1458" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>
<DIV><FONT face=3DArial size=3D2>Dear all,<BR><BR>I need to do something si=
milar to=20
a cross tabulation, but without any<BR>aggregation.<BR><BR>I have below=20
table<BR><BR>&nbsp;id | employee_id |&nbsp;&nbsp; state&nbsp;&nbsp;=20
|&nbsp;&nbsp;&nbsp;&nbsp;=20
check_time<BR>----+-------------+-----------+--------------------<BR>&nbsp;=
21=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 |=20
In&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 2004-10-12 21:37:13<BR>&nbsp=
;22=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 | Break Out=
 |=20
2004-10-12 21:37:31<BR>&nbsp;23=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 | Break In&=
nbsp;=20
| 2004-10-12 21:37:42<BR>&nbsp;24=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 |=20
Out&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 2004-10-12 21:37:50<BR>&nbsp;25=
=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 |=20
In&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 2004-10-13 19:20:36<BR>&nbsp=
;26=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 |=20
In&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 2004-10-14 01:33:48<BR>&nbsp=
;27=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 | Break Out=
 |=20
2004-10-14 01:59:15<BR>&nbsp;28=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 | Break In&=
nbsp;=20
| 2004-10-14 03:15:45<BR>&nbsp;29=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 |=20
Out&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 2004-10-14 03:17:23<BR>&nbsp;30=
=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3 |=20
In&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 2004-10-14 03:17:43<BR>&nbsp=
;31=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3 | Break Out=
 |=20
2004-10-14 19:32:34<BR>&nbsp;32=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 |=20
In&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 2004-10-14 20:34:15<BR>&nbsp=
;33=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3 |=20
In&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 2004-10-15 02:01:28<BR>&nbsp=
;34=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3 | Break Out=
 |=20
2004-10-15 02:02:07<BR>&nbsp;35=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3 |=20
In&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 2004-10-16 02:06:43<BR>&nbsp=
;36=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 |=20
In&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 2004-10-16 02:07:33<BR>&nbsp=
;37=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 | Break Out=
 |=20
2004-10-16 02:09:09<BR>&nbsp;38=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 | Break In&=
nbsp;=20
| 2004-10-16 04:10:21<BR>&nbsp;39=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 |=20
Out&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 2004-10-16 04:12:27<BR>&nbsp;40=
=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3 | Break Out=
 |=20
2004-10-16 21:38:22</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV>I need something like this:</DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp; date&nbsp;&nbsp;&nbsp; | empl=
oyee_id=20
|&nbsp;&nbsp;&nbsp; in&nbsp;&nbsp;&nbsp; | break_out | break_id |&nbsp;&nbs=
p;=20
out<BR>-----------+-------------+----------+-----------+----------+--------=
--<BR>2004-10-12=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 | 21:37:13 =
|=20
21:37:31&nbsp; | 21:37:42 |21:37:50<BR>2004-10-14=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 | 01:33:48 =
|=20
01:59:15&nbsp; | 03:15:45 |03:17:23<BR>2004-10-14=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3 | 03:17:43 =
|=20
19:32:34&nbsp; | 03:15:45 |03:17:23&nbsp;&nbsp;=20
<BR>........<BR>........<BR>........<BR></FONT><BR></DIV>
<DIV>&nbsp;</DIV></FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_011C_01C4B6A2.551C1F00--





From gsstark@mit.edu Wed Oct  20 06:35:40 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 28B3532AA2E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 20 Oct 2004 07:35:40 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 08319-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 20 Oct 2004 06:35:34 +0000 (GMT)
Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162])
	by svr1.postgresql.org (Postfix) with ESMTP id 0B07332A9DB
	for <pgsql-sql@postgresql.org>; Wed, 20 Oct 2004 07:35:34 +0100 (BST)
Received: from localhost ([127.0.0.1] helo=stark.xeocode.com)
	by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian))
	id 1CKA4D-0006PK-00; Wed, 20 Oct 2004 02:35:25 -0400
To: "Muhyiddin A.M Hayat" <middink@indo.net.id>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Cross tabulations
References: <012501c4b65f$9b70b620$4f00a8c0@middinks>
In-Reply-To: <012501c4b65f$9b70b620$4f00a8c0@middinks>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 20 Oct 2004 02:35:25 -0400
Message-ID: <878ya128ya.fsf@stark.xeocode.com>
Lines: 34
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200410/139
X-Sequence-Number: 19242

"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




From middink@indo.net.id Mon Oct  25 08:50:20 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id D5F133A3B4C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 25 Oct 2004 09:50:20 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 96656-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 25 Oct 2004 08:50:08 +0000 (GMT)
Received: from smtp-1.indo.net.id (smtp-1.indo.net.id [202.159.32.91])
	by svr1.postgresql.org (Postfix) with SMTP id 8E9983A2B23
	for <pgsql-sql@postgresql.org>; Mon, 25 Oct 2004 09:50:04 +0100 (BST)
Received: (qmail 7289 invoked from network); 25 Oct 2004 15:49:55 +0700
Received: from smtp.indo.net.id (202.159.32.81)
	by smtp-1.indo.net.id with SMTP; 25 Oct 2004 15:49:55 +0700
Received: (qmail 14124 invoked from network); 25 Oct 2004 15:49:54 +0700
Received: from unknown (HELO middinks) (202.159.122.101)
	by smtp.indo.net.id with SMTP; 25 Oct 2004 15:49:54 +0700
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
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200410/166
X-Sequence-Number: 19269

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
>