diff between 2 DATETIMEs, SUMmed & GROUPed BY project

[Jim Michaels]

SELECT id,name, prj,SUM(TIMEDIF F(end_time,star t_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.

[Jim Michaels]

Re: diff between 2 DATETIMEs, SUMmed & GROUPed BY project


"Jim Michaels" <NOSPAMFORjmich ae3@yahoo.com> wrote in message
news:ZqednSaWA8 5fz4rZ4p2dnA@co mcast.com...[color=blue]
> SELECT id,name, prj,SUM(TIMEDIF F(end_time,star t_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.
>[/color]

CREATE VIEW workhourstot AS
SELECT id,name, prj,sum(hour(ti mediff(end_time ,start_time))) AS hour,
sum(minute(time diff(end_time,s tart_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.

[Jim Michaels]

Re: diff between 2 DATETIMEs, SUMmed &amp; GROUPed BY project


"Jim Michaels" <NOSPAMFORjmich ae3@yahoo.com> wrote in message
news:TO2dnSdpRu xxwIrZRVn-qQ@comcast.com. ..[color=blue]
>
> "Jim Michaels" <NOSPAMFORjmich ae3@yahoo.com> wrote in message
> news:ZqednSaWA8 5fz4rZ4p2dnA@co mcast.com...[color=green]
>> SELECT id,name, prj,SUM(TIMEDIF F(end_time,star t_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.
>>[/color]
>
> CREATE VIEW workhourstot AS
> SELECT id,name, prj,sum(hour(ti mediff(end_time ,start_time))) AS hour,
> sum(minute(time diff(end_time,s tart_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.
>[/color]

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