|
|
Posted by Erland Sommarskog on 10/03/07 22:00
(tatata9999@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|