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 s,

Palli

<DonLi2006@gmail.com> wrote in message
news:1190084992.933315.305940@g4g2000hsf.googlegro ups.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.
>

Beautiful, thank you.

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 s,
>
> Palli
>
> <DonLi2...@gmail.com> wrote in message
>
> news:1190084992.933315.305940@g4g2000hsf.googlegro ups.com...
>
>
>
> > Hi,
> OP omitted
> - Show quoted text -

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 me to
think, oh, you may ask, may I eat for you as well? thanks a
billion...

On Sep 18, 10:58 am, DonLi2...@gmail.com wrote:
> Beautiful, thank you.
>
> 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 s,
>
> > Palli
>
> > <DonLi2...@gmail.com> wrote in message
>
> >news:1190084992.933315.305940@g4g2000hsf.googlegr oups.com...
>
> > > Hi,
> > OP omitted
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

DonLi2006@gmail.com 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

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...@socal.rr.com> wrote:
> DonLi2...@gmail.com 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
>
> 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

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 s,
>
> Palli
>
> <DonLi2...@gmail.com> wrote in message
>
> news:1190084992.933315.305940@g4g2000hsf.googlegro ups.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

(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 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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx