On 2011-06-09, Andreas wrote:
> Am 09.06.2011 18:20, schrieb Richard Broersma:
>> On Thu, Jun 9, 2011 at 6:43 AM, Andreas wrote:
>>
>>> I have a log-table that stores events of users and projects like this
>>> ( user_id integer, project_id integer, ts timestamp, event_type integer )
>>>
>>> I nee
Try this:
select user_id, project_id, date_trunc, sum(sum) FROM (select user_id,
project_id, date_trunc('day', ts), SUM(duration) FROM (select user_id,
project_id, a.ts, ((SELECT MIN(b.ts) FROM log b WHERE b.ts>a.ts AND
(date_trunc('day',a.ts)=date_trunc('day',b.ts)))-a.ts) AS duration
from log a
Am 09.06.2011 18:20, schrieb Richard Broersma:
On Thu, Jun 9, 2011 at 6:43 AM, Andreas wrote:
I have a log-table that stores events of users and projects like this
( user_id integer, project_id integer, ts timestamp, event_type integer )
I need an aggregated list of worktime per user, per pro
On Thu, Jun 9, 2011 at 6:43 AM, Andreas wrote:
> I have a log-table that stores events of users and projects like this
> ( user_id integer, project_id integer, ts timestamp, event_type integer )
>
> I need an aggregated list of worktime per user, per project, per day.
>
> The users can switch pro
an user cannot be in more
than one project at the time? If so, can't be overlapping, right?
Best,
Oliveiros
- Original Message - From: "Andreas"
To:
Sent: Thursday, June 09, 2011 2:43 PM
Subject: [SQL] need help with some aggregation magic
hi,
I have a log-table tha
;t be overlapping, right?
Best,
Oliveiros
- Original Message -
From: "Andreas"
To:
Sent: Thursday, June 09, 2011 2:43 PM
Subject: [SQL] need help with some aggregation magic
hi,
I have a log-table that stores events of users and projects like this
( user_id integer, project
hi,
I have a log-table that stores events of users and projects like this
( user_id integer, project_id integer, ts timestamp, event_type integer )
I need an aggregated list of worktime per user, per project, per day.
The users can switch projects during the day so I can't work this out
with mi
