Posted by tatata9999 on 10/04/07 00:09

On Oct 3, 5:00 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (tatata9...@gmail.com) writes:
> > Hi, there's a bug. The following query would return what is expected,
> > good.
> > select pkCol, cddate as origdate,convert(char,cddate,101) as
> > ddate, start, stop, project,
> > CASE WHEN (datediff(n,start,stop) < 0) THEN
> > Left((datediff(n,start,'23:59') + datepart(n,'2007-09-19
> > 10:01:00') + datediff(n,'00:00',stop))/60.0,4)
> > ELSE Left(datediff(n,start,stop)/60.0,4) END as
> > hours_spent
> > from testTBL
>
> > However, when switching to sum function for the above like, I'm
> > getting invalid results, the culprit seems to be the entry/entries
> > with two dates overlap, see a sample below the following query? And
> > the odd thing is, when I tested the query against this particular
> > entry, it generated correct resultset (summary), but not a query like
> > the one below, how come and more importantly how to fix it? Thanks.
> > select project,
> > CASE WHEN (SUM(datediff(n,start,stop)/60.0) < 0)
> > THEN Left(SUM((datediff(n,start,'23:59') +
> > datepart(n,'2007-09-19 10:01:00') + datediff(n,'00:00',stop))/60.0),
> > 4)
> > WHEN (SUM(datediff(n,start,stop)/60.0) > 0)
> > THEN Left(SUM(datediff(n,start,stop)/60.0),4) End as
> > total_hours
> > from testTBL
> > group by project
>
> It could help if you posted the CREATE TABLE statement for the table,
> INSERT statments with sample data, and the desired result. I can't
> exactly see what you are looking for. But one think looks funny to
> me: you have SUM on every expression in the CASE. I would expect the
> SUM to be around the entire CASE. But as I said, I don't know what
> this query is supposed to achieve.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
> - Show quoted text -

Erland,

You're the Man! Thank you.

[Back to original message]