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