Help with query, stuck :(

Поиск
Список
Период
Сортировка
От bumby
Тема Help with query, stuck :(
Дата
Msg-id 3b287bee0506231003213f6c6e@mail.gmail.com
обсуждение исходный текст
Список pgsql-sql
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 zonefinish   | 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


В списке pgsql-sql по дате отправления:

Предыдущее
От: "Riya Verghese"
Дата:
Сообщение: ORDER records based on parameters in IN clause
Следующее
От: orange_crush_068@hotmail.com
Дата:
Сообщение: Unique primary index?