Posted by tatata9999 on 10/07/07 01:42

On Oct 4, 10:31 pm, tatata9...@gmail.com wrote:
> 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?

Update: odd. When I tried another set of data, for a total of 100
hours, using your technique, the difference between sum query and
regular query is now less than an hour, not too bad. But what if we
run into 1000 hours?

[Back to original message]