time conversion hiccup

am 18.09.2007 05:09:52 von DonLi2006

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.

Re: time conversion hiccup

am 18.09.2007 16:43:10 von Pall Bjornsson

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

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.
>

Re: time conversion hiccup

am 18.09.2007 17:58:44 von DonLi2006

Beautiful, thank you.

On Sep 18, 9:43 am, "Pall Bjornsson" 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
>
> wrote in message
>
> news:1190084992.933315.305940@g4g2000hsf.googlegroups.com...
>
>
>
> > Hi,
> OP omitted
> - Show quoted text -

Re: time conversion hiccup

am 19.09.2007 02:07:50 von DonLi2006

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...@gmail.com wrote:
> Beautiful, thank you.
>
> On Sep 18, 9:43 am, "Pall Bjornsson" 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
>
> > wrote in message
>
> >news:1190084992.933315.305940@g4g2000hsf.googlegroups.com.. .
>
> > > Hi,
> > OP omitted
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Re: time conversion hiccup

am 19.09.2007 16:32:42 von Ed Murphy

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

Re: time conversion hiccup

am 19.09.2007 23:47:46 von DonLi2006

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

On Sep 19, 9:32 am, Ed Murphy 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

Re: time conversion hiccup

am 03.10.2007 17:43:44 von tatata9999

On Sep 18, 9:43 am, "Pall Bjornsson" 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
>
> 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

Re: time conversion hiccup

am 04.10.2007 00:00:28 von Erland Sommarskog

(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 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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: time conversion hiccup

am 04.10.2007 02:09:18 von tatata9999

On Oct 3, 5:00 pm, Erland Sommarskog wrote:
> (tatata9...@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 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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx- Hide quoted text -
>
> - Show quoted text -

Erland,

You're the Man! Thank you.

Re: time conversion hiccup

am 04.10.2007 02:19:05 von tatata9999

Re: time conversion hiccup

am 04.10.2007 04:30:19 von Ed Murphy

tatata9999@gmail.com wrote:

> 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

CASTing datediff() to some appropriate DECIMAL type should take
care of it.

Re: time conversion hiccup

am 05.10.2007 00:27:14 von tatata9999

On Oct 3, 9:30 pm, Ed Murphy wrote:
> tatata9...@gmail.com wrote:
> > 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
>
> CASTing datediff() to some appropriate DECIMAL type should take
> care of it.

I've tried DECIMAL(1) and (2) respectively to no avail. Do you have a
sample one? Thanks.

Re: time conversion hiccup

am 05.10.2007 02:59:55 von Ed Murphy

tatata9999@gmail.com wrote:

> On Oct 3, 9:30 pm, Ed Murphy wrote:
>> tatata9...@gmail.com wrote:
>>> 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
>> CASTing datediff() to some appropriate DECIMAL type should take
>> care of it.
>
> I've tried DECIMAL(1) and (2) respectively to no avail. Do you have a
> sample one? Thanks.

Try DECIMAL(10,2) and see how that works for you.

Re: time conversion hiccup

am 05.10.2007 05:31:26 von tatata9999

On Oct 4, 7:59 pm, Ed Murphy wrote:
> tatata9...@gmail.com wrote:
> > On Oct 3, 9:30 pm, Ed Murphy wrote:
> >> tatata9...@gmail.com wrote:
> >>> 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
> >> CASTing datediff() to some appropriate DECIMAL type should take
> >> care of it.
>
> > I've tried DECIMAL(1) and (2) respectively to no avail. Do you have a
> > sample one? Thanks.
>
> Try DECIMAL(10,2) and see how that works for you.

Thank you, this is a good idea to try. Here's some sample result,
before I do that, let me refresh ddl a bit for clarity,
both start and stop columns are of char(5) nullable.
The query looks like this
select SUM(Convert(DECIMAL(10,2), CASE WHEN (datediff(n,start,stop)/60
< 0)
THEN (datediff(n,start,'23:59') +
datepart(n,'2007-09-19 10:01:00') + datediff(n,'00:00',stop)/60.0)
WHEN (datediff(n,start,stop)/60 > 0)
THEN (datediff(n,start,stop)/60.0) ...
Also, I tried the DECIMAL(10,2) and its variants for a regular query,
then use app language to total it.
The difference is, the sum one is 94.80 hours while the regular query
is 90.24. Not satisfactory.

I've also looked up BOL for it, and tried different p/s variants to no
avail. Hmm, am I stuck?

Re: time conversion hiccup

am 05.10.2007 09:16:34 von Erland Sommarskog

(tatata9999@gmail.com) writes:
> I've tried DECIMAL(1) and (2) respectively to no avail. Do you have a
> sample one? Thanks.

Decimal(1) means a number in the range 0-9 with no decimals.

I usually sort this out by simply multiplying with 1.0. Like in many other
languages, / in T-SQL is integer division when two integers meet.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: time conversion hiccup

am 07.10.2007 03:42:29 von tatata9999

On Oct 4, 10:31 pm, tatata9...@gmail.com wrote:
> On Oct 4, 7:59 pm, Ed Murphy wrote:
>
> > tatata9...@gmail.com wrote:
> > > On Oct 3, 9:30 pm, Ed Murphy wrote:
> > >> tatata9...@gmail.com wrote:
> > >>> 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
> > >> CASTing datediff() to some appropriate DECIMAL type should take
> > >> care of it.
>
> > > I've tried DECIMAL(1) and (2) respectively to no avail. Do you have a
> > > sample one? Thanks.
>
> > Try DECIMAL(10,2) and see how that works for you.
>
> Thank you, this is a good idea to try. Here's some sample result,
> before I do that, let me refresh ddl a bit for clarity,
> both start and stop columns are of char(5) nullable.
> The query looks like this
> select SUM(Convert(DECIMAL(10,2), CASE WHEN (datediff(n,start,stop)/60
> < 0)
> THEN (datediff(n,start,'23:59') +
> datepart(n,'2007-09-19 10:01:00') + datediff(n,'00:00',stop)/60.0)
> WHEN (datediff(n,start,stop)/60 > 0)
> THEN (datediff(n,start,stop)/60.0) ...
> Also, I tried the DECIMAL(10,2) and its variants for a regular query,
> then use app language to total it.
> The difference is, the sum one is 94.80 hours while the regular query
> is 90.24. Not satisfactory.
>
> I've also looked up BOL for it, and tried different p/s variants to no
> avail. Hmm, am I stuck?

Update: odd. When I tried another set of data, for a total of 100
hours, using your technique, the difference between sum query and
regular query is now less than an hour, not too bad. But what if we
run into 1000 hours?

Re: time conversion hiccup

am 07.10.2007 03:43:55 von tatata9999

On Oct 5, 2:16 am, Erland Sommarskog wrote:
> (tatata9...@gmail.com) writes:
> > I've tried DECIMAL(1) and (2) respectively to no avail. Do you have a
> > sample one? Thanks.
>
> Decimal(1) means a number in the range 0-9 with no decimals.
>
> I usually sort this out by simply multiplying with 1.0. Like in many other
> languages, / in T-SQL is integer division when two integers meet.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Interesting idea, Erland, but it did not seem to work for me, maybe I
didn't do it right? Only two variations.

Re: time conversion hiccup

am 07.10.2007 16:36:22 von Erland Sommarskog

(tatata9999@gmail.com) writes:
> Interesting idea, Erland, but it did not seem to work for me, maybe I
> didn't do it right? Only two variations.

We have only seen fragments and pieces of what you have been doing. It
would help if you posted a create table statement for your table, insert
statements with sample data, and the desired result given the sample.
That makes it easy to develop tested query. Without that, we are mainly
guessing.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: time conversion hiccup

am 08.10.2007 18:48:52 von tatata9999

On Oct 7, 9:36 am, Erland Sommarskog wrote:
> (tatata9...@gmail.com) writes:
> > Interesting idea, Erland, but it did not seem to work for me, maybe I
> > didn't do it right? Only two variations.
>
> We have only seen fragments and pieces of what you have been doing. It
> would help if you posted a create table statement for your table, insert
> statements with sample data, and the desired result given the sample.
> That makes it easy to develop tested query. Without that, we are mainly
> guessing.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Very fair. Thank you, and Ed Murphy. Now, the problem is, when I
apply either one of the following sum queries, the result is about 30
hours short of 114 hours, how come? Could my data be compromised?
Or?

-- ddl
CREATE TABLE [dbo].[myTBL] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[mydate] [datetime] NOT NULL default getDate(),
[proj] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[start] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stop] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TRIGGER populateStartOrNot4myTBL
ON dbo.myTBL
AFTER INSERT AS
If exists (select *
from thisTEMP -- for business logic / business rule
where autostart = 1)
begin
UPDATE myTBL
SET start = Cast(DatePart(hh,getDate()) as varchar(2)) + ':' +
Cast(DatePart(n,getDate()) as varchar(2))
WHERE id = (SELECT id FROM inserted)
end

-- sample dml
insert into myTBL(proj, start, stop)
values ('test me','17:00','17:55');
insert into myTBL(proj, start, stop)
values ('test me','8:00','14:25');

insert into myTBL(proj, start)
values ('test me','23:25');
-- suppose, the above one insert, create ID, 3
update myTBL
set stop = '1:15'
where ID = 3
-- now for the row 3, it spans two days

-- reg query 1
select ID, mydate as origdate,convert(char,mydate,101) as mydate,
proj, start, stop,
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 myTBL
order by origdate DESC
-- comment: looks good
-- reg q 2
select ID, mydate as origdate,convert(char,mydate,101) as mydate,
proj, start, stop,
CASE WHEN (datediff(n,start,stop) < 0) THEN
(datediff(n,start,stop) + 1440)/60.0
ELSE Left(datediff(n,start,stop)/60.0,4) END as hours_spent
from myTBL
order by origdate DESC
-- comment: looks good

-- sum
-- try 1
set ANSI_WARNINGS OFF;

select proj, SUM(CASE WHEN (datediff(n,start,stop)/60 < 0)
THEN datediff(n,start,'23:59') + datepart(n,'2007-09-19
10:01:00') + datediff(n,'00:00',stop)/60
WHEN (datediff(n,start,stop)/60 > 0)
THEN (datediff(n,start,stop)/60)
End) * 1.0 as total_hours
from myTBL
-- where ID < 3
group by proj with rollup

set ANSI_WARNINGS ON;
-- comment: bad

-- try 2
set ANSI_WARNINGS OFF;

select proj, SUM(CASE WHEN (datediff(n,start,stop)/60 < 0)
THEN (datediff(n,start,stop) + 1440)/60.0
WHEN (datediff(n,start,stop)/60 > 0)
THEN (datediff(n,start,stop)/60.0)
End) as total_hours
from myTBL
group by proj with rollup

set ANSI_WARNINGS ON;

-- comment: not bad

-- try 3

set ANSI_WARNINGS OFF;
select proj, SUM(convert(decimal(10,2),CASE WHEN
(datediff(n,start,stop)/60 < 0)
THEN (datediff(n,start,stop) + 1440)/60.0
WHEN (datediff(n,start,stop)/60 > 0)
THEN (datediff(n,start,stop)/60.0)
End)) as total_hours
from myTBL
group by proj with rollup

-- comment: not bad

Re: time conversion hiccup

am 08.10.2007 18:59:24 von tatata9999

Re: time conversion hiccup

am 08.10.2007 23:46:07 von Erland Sommarskog

(tatata9999@gmail.com) writes:
> Good News! Problem resolved. Culprit, 'WHEN (datediff(n,start,stop)/
> 60 > 0)' needs to be
> WHEN (datediff(n,start,stop)/60.0 > 0)

Great to hear! Thanks for posting back and telling us. I'm taking the
liberty to not look at your scripts then.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx