Posted by tatata9999 on 10/03/07 15:43

On Sep 18, 9:43 am, "Pall Bjornsson" <pa...@kvos.is> wrote:
> Hi !
>
> What I can see via quick read are two errors or mistakes.
>
> 1) Definition of a variable or result of type decimal(1), can store at the
> most one total number of digits both to the left and to the right of the
> decimal point, so you'll never get a result with anything more than a single
> digit number, even if the result should be 10 or more, in which case you
> should get an overflow error.
>
> 2) The division by the integer number 60 forces the operation to be an
> integer division, as you can easily see by executing this statement:
> select datediff(n,'08:00','14:25')/60,
>
> convert(decimal(1),datediff(n,'08:00','14:25')/60),
>
> datediff(n,'08:00','14:25')/60.0,
>
> convert(decimal(1),datediff(n,'08:00','14:25')/60.0)
>
> Hope this helps,
>
> Palli
>
> <DonLi2...@gmail.com> wrote in message
>
> news:1190084992.933315.305940@g4g2000hsf.googlegroups.com...
>
>
>
> > Hi,
>
> > ddl & dml
> > project varchar(10) start char(5) stop char(5)
> > ------------------------- ----- -----
> > hey now 21:00 19:25
> > new test 20:25 20:30
> > t 10 21:00 NULL
> > t 11 21:10 21:35
> > t 12 21:30 22:40
> > t 12 7:05 11:10
> > test me 08:00 14:25
> > test me 17:00 17:55
>
> > what I want is to calculate time duration using hour (h.1decimal) e.g.
> > 1.2 :
> > what I have now using the following query:
> > select project, start, stop,
> > CASE WHEN (datediff(n,start,stop) < 0) THEN -1
> > WHEN (datediff(n,start,stop) < 1) THEN (CAST(datediff(n,start,stop)
> > as decimal(1)))
> > ELSE Convert(decimal(1),(datediff(n,start,stop)/60)) END as
> > total_hours
> > from testTBl
> > group by project, start, stop
>
> > output:
> > project start stop total_hours
> > ------------------------- ----- ----- -----------
> > hey now 21:00 19:25 -1
> > new test 20:25 20:30 0
> > t 10 21:00 NULL NULL
> > t 11 21:10 21:35 0
> > t 12 21:30 22:40 1
> > t 12 7:05 11:10 4
> > test me 08:00 14:25 6
> > test me 17:00 17:55 0
>
> > If the calcuate is right I'd like to remove start and stop columns,
> > so, it would just return project and the sum of hours including less
> > than an hour in decimal for each.
>
> > Thank you.- Hide quoted text -
>
> - Show quoted text -

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

> >cddate project start stop
> > ----------- ------------ ----- ----- ----------- ------
> > 10/2/2007 hey now 23:05 1:15

[Back to original message]