Posted by tatata9999 on 10/05/07 03:31

On Oct 4, 7:59 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
> tatata9...@gmail.com wrote:
> > On Oct 3, 9:30 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
> >> tatata9...@gmail.com wrote:
> >>> SUM(CASE WHEN (datediff(n,start,stop)/60 > 0)
> >>> THEN (datediff(n,start,stop)/60) End) as
> >>> total_hours
> >>> returned summary/calculated about right, but it's at hour level, so,
> >>> 0.45 minutes would be discarded, not very good
> >> CASTing datediff() to some appropriate DECIMAL type should take
> >> care of it.
>
> > I've tried DECIMAL(1) and (2) respectively to no avail. Do you have a
> > sample one? Thanks.
>
> Try DECIMAL(10,2) and see how that works for you.

Thank you, this is a good idea to try. Here's some sample result,
before I do that, let me refresh ddl a bit for clarity,
both start and stop columns are of char(5) nullable.
The query looks like this
select SUM(Convert(DECIMAL(10,2), CASE WHEN (datediff(n,start,stop)/60
< 0)
THEN (datediff(n,start,'23:59') +
datepart(n,'2007-09-19 10:01:00') + datediff(n,'00:00',stop)/60.0)
WHEN (datediff(n,start,stop)/60 > 0)
THEN (datediff(n,start,stop)/60.0) ...
Also, I tried the DECIMAL(10,2) and its variants for a regular query,
then use app language to total it.
The difference is, the sum one is 94.80 hours while the regular query
is 90.24. Not satisfactory.

I've also looked up BOL for it, and tried different p/s variants to no
avail. Hmm, am I stuck?

[Back to original message]