Cross tabulations

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 =

.........
.........
.........






------=_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
a cross tabulation, but without any<BR>aggregation.<BR><BR>I have below
table<BR><BR> id | employee_id |   state  
|    
check_time<BR>----+-------------+-----------+--------------------<BR> =
21
|           1 |
In        | 2004-10-12 21:37:13<BR> =
;22
|           1 | Break Out=
|
2004-10-12 21:37:31<BR> 23
|           1 | Break In&=
nbsp;
| 2004-10-12 21:37:42<BR> 24
|           1 |
Out       | 2004-10-12 21:37:50<BR> 25=

|           1 |
In        | 2004-10-13 19:20:36<BR> =
;26
|           1 |
In        | 2004-10-14 01:33:48<BR> =
;27
|           1 | Break Out=
|
2004-10-14 01:59:15<BR> 28
|           1 | Break In&=
nbsp;
| 2004-10-14 03:15:45<BR> 29
|           1 |
Out       | 2004-10-14 03:17:23<BR> 30=

|           3 |
In        | 2004-10-14 03:17:43<BR> =
;31
|           3 | Break Out=
|
2004-10-14 19:32:34<BR> 32
|           2 |
In        | 2004-10-14 20:34:15<BR> =
;33
|           3 |
In        | 2004-10-15 02:01:28<BR> =
;34
|           3 | Break Out=
|
2004-10-15 02:02:07<BR> 35
|           3 |
In        | 2004-10-16 02:06:43<BR> =
;36
|           1 |
In        | 2004-10-16 02:07:33<BR> =
;37
|           1 | Break Out=
|
2004-10-16 02:09:09<BR> 38
|           1 | Break In&=
nbsp;
| 2004-10-16 04:10:21<BR> 39
|           1 |
Out       | 2004-10-16 04:12:27<BR> 40=

|           3 | Break Out=
|
2004-10-16 21:38:22</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV>I need something like this:</DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>   date    | empl=
oyee_id
|    in    | break_out | break_id | &nbs=
p;
out<BR> -----------+-------------+----------+-----------+----------+ --------=
--<BR>2004-10-12
|           1 | 21:37:13 =
|
21:37:31  | 21:37:42 |21:37:50<BR>2004-10-14
|           1 | 01:33:48 =
|
01:59:15  | 03:15:45 |03:17:23<BR>2004-10-14
|           3 | 03:17:43 =
|
19:32:34  | 03:15:45 |03:17:23  
<BR>........<BR>........<BR>........<BR></FONT><BR></DIV>
<DIV> </DIV></FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV></BODY></HTML>

------=_NextPart_000_011C_01C4B6A2.551C1F00--
middink [ Mi, 20 Oktober 2004 06:43 ] [ ID #453849 ]

Re: Cross tabulations

"Muhyiddin A.M Hayat" <middink [at] 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
gsstark [ Mi, 20 Oktober 2004 08:35 ] [ ID #453850 ]

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.edu>
To: "Muhyiddin A.M Hayat" <middink [at] indo.net.id>
Cc: <pgsql-sql [at] postgresql.org>
Sent: Wednesday, October 20, 2004 2:35 PM
Subject: Re: [SQL] Cross tabulations


> "Muhyiddin A.M Hayat" <middink [at] 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
>



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo [at] postgresql.org
middink [ Mo, 25 Oktober 2004 10:49 ] [ ID #453878 ]
Datenbanken » comp.databases.postgresql.sql » Cross tabulations

Vorheriges Thema: bibliographic schema
Nächstes Thema: Create interval using column value?

[ Startseite ] [ Administrator ] [ Suche ] [ Hinweise ] [ Impressum ]

Powered by FudForum