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.

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 (AT) gmail (DOT) com> wrote

Beautiful, thank you.

On Sep 18, 9:43 am, "Pall Bjornsson" <pa... (AT) kvos (DOT) is> wrote:

ahe, I spoke a bit too soon, new prob.
data sets:
start stop
19:30 02:15 (next day morning)
26:15 (invalid hh:mm time range)

CASE WHEN (datediff(n,start,stop) < 0) THEN 0 END

above stmt not good, what now? got to go eat, could you help me to
think, oh, you may ask, may I eat for you as well? thanks a
billion...

On Sep 18, 10:58 am, DonLi2... (AT) gmail (DOT) com wrote:

DonLi2006 (AT) gmail (DOT) com wrote:

Assuming that the stop time is always within 24 hours after the
start time:

case
when datediff(n,start,stop) < 0
then datediff(n,start,stop) + 1440 -- minutes per day
else datediff(n,start,stop)
end

Yeah, I solved it in a similar fasion this morning, sorry for the late
update.

On Sep 19, 9:32 am, Ed Murphy <emurph... (AT) socal (DOT) rr.com> wrote:

On Sep 18, 9:43 am, "Pall Bjornsson" <pa... (AT) kvos (DOT) is> wrote:
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

(tatata9999 (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Oct 3, 5:00 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Erland,

You're the Man! Thank you.

On Oct 3, 5:00 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
oops, I hit the response button too fast. Now,
option a:
SUM(CASE WHEN (datediff(n,start,stop)/60 > 0)
THEN (datediff(n,start,stop)/60) End) as
total_hours
returned summary/calculated about right, but it's at hour level, so,
0.45 minutes would be discarded, not very good

option b:
SUM(CASE WHEN (datediff(n,start,stop)/60 > 0)
THEN (datediff(n,start,stop)/60.0) End) as
total_hours
returned bloated up data (too much), not good at all

What else? As always, many thanks.