DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=RTAC7r/7QjSRvh8H04v9cfTObvaNq/j2XG3MOxxj4TYxGLiUAq5U8aYTqdE0bOjP9K2fBfx/8spRtbp6yq7gqL9f6xu4Xu9Z4upWMvLXQR5L8Kkr1gdfjhNPLa/ZM4mXxw74w4A0WYH6NpBnLYJ5PsRyqyZwHs4joUXCTd69DB4=
Message-ID: <3b287bee0506231003213f6c6e@mail.gmail.com>
Date: Thu, 23 Jun 2005 19:03:07 +0200
From: bumby <bumbyn@gmail.com>
Reply-To: bumby <bumbyn@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Help with query, stuck :(
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

I have a table as

                                     Table "public.timereport"
  Column  |            Type             |                       =20
Modifiers
----------+-----------------------------+----------------------------------=
--------------------------
 id       | integer                     | not null default
nextval('public.timereport_id_seq'::text)
 employee | character varying(10)       |=20
 start    | timestamp without time zone | default
('now'::text)::timestamp without time zone
 finish   | timestamp without time zone |=20
 break    | interval                    |=20
 flags    | character(8)                |=20
 authed   | boolean                     | default false


I want to do a SUM, AVG, MAX and MIN on finish-start-break to get the
total time worked for the periode stated in the WHERE expression.
(like where employee=3D'Martin' and extract(week from start)=3D5). I also
want to calculate an overtime like

SUM(finish-start-break::interval)-COUNT(*)*'8 hours'::interval

Problem is, as I noticed, that if one signs in and out more then one
time per day, the overtime calculation will not work (since I subtract
8 hours for each sign-in/out.

So I guess I'll have to do a distinct select on date_trunc('day',
start). Best I can come up with is

SELECT
SUM(x.finish-x.start-x.break::interval) as stat_tot,
AVG(x.finish-x.start-x.break::interval) as stat_avg,
MAX(x.finish-x.start-x.break::interval) as stat_max,
MIN(x.finish-x.start-x.break::interval) as stat_min,
SUM(x.finish-x.start-x.break::interval)-COUNT(*)*'8 hours'::interval
as stat_otime
FROM ( =20
        SELECT DISTINCT ON (date_trunc('day', start)) * FROM
timereport WHERE employee=3D'Martin'
) AS x =20
GROUP BY id,start,employee ORDER BY date_trunc('day', start), id, employee;

This however returns one SUM,AVG, etc row for each row in the
sub-query instead of doing what I want - having the sub-query return
all rows and letting SUM etc function do their work on those rows.

Any suggestions?

Thanks in advance,

bumby