Posted by DonLi2006 on 09/18/07 03:09

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.

• Next in forum: Retrieve ONLY first/max
• Prev in forum: Re: How would you... (binary info in a table)
• Thread view: time conversion hiccup

[Reply to this message]