|
|
Posted by Pall Bjornsson on 09/18/07 14:43
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
<DonLi2006@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.
>
Navigation:
[Reply to this message]
|