I have a table as
Table "public.timereport"
Column | Type |
Modifiers
----------+-----------------------------+------------------------------------------------------------
id | integer | not null default
nextval('public.timereport_id_seq'::text)
employee | character varying(10) |
start | timestamp without time zone | default
('now'::text)::timestamp without time zone
finish | timestamp without time zone |
break | interval |
flags | character(8) |
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='Martin' and extract(week from start)=5). 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 (
SELECT DISTINCT ON (date_trunc('day', start)) * FROM
timereport WHERE employee='Martin'
) AS x
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
