Posted by Jim Michaels on 03/19/06 10:28

"Jim Michaels" <NOSPAMFORjmichae3@yahoo.com> wrote in message
news:TO2dnSdpRuxxwIrZRVn-qQ@comcast.com...
>
> "Jim Michaels" <NOSPAMFORjmichae3@yahoo.com> wrote in message
> news:ZqednSaWA85fz4rZ4p2dnA@comcast.com...
>> SELECT id,name, prj,SUM(TIMEDIFF(end_time,start_time)) AS elapsed
>> FROM work
>> group by name,prj;
>>
>> The problem is, I need elapsed in a datetime format, and SUM truncates to
>> integer hours.
>>
>> Is there no way to do this?
>> I just want the group sums as hours, minutes, dats, months, years.
>>
>
> CREATE VIEW workhourstot AS
> SELECT id,name, prj,sum(hour(timediff(end_time,start_time))) AS hour,
> sum(minute(timediff(end_time,start_time))) AS minute
> FROM work
> GROUP BY name,prj;
>
> This worked for me I guess, but one of the minutes ended up as 70. I
> think it's because of the grouped sum. ugh. I need a solution.
>
> datediff is out of the question.
>

figured it out.
CREATE VIEW workhourstot AS
SELECT id,name, prj,(SUM(MINUTE(TIMEDIFF(end_time,start_time))) DIV
60)+SUM(HOUR(TIMEDIFF(end_time,start_time))) AS hour,
MOD(SUM(MINUTE(timediff(end_time,start_time))),60) AS minute
FROM work
GROUP BY name,prj;

[Back to original message]