Peter, Baron and all,
I think that I am almost there. Here's my query to return
cycle time in hours:minutes:seconds accounting for business hours. There
is some discussion as to when a ticket cycle time ends - for example if
a person works on a ticket at 10:00 at night and closes it,
Wow, thanks. Lots to think about.
From: Peter Brawley [mailto:[EMAIL PROTECTED]
Sent: Monday, August 27, 2007 10:18 PM
To: Weston, Craig (OFT)
Cc: mysql@lists.mysql.com
Subject: Re: [MYSQL]Time formatting for cycle time.
Craig,
I am working on Martin
To: Weston, Craig (OFT)
Cc: mysql@lists.mysql.com
Subject: Re: [MYSQL]Time formatting for cycle time.
Craig,
I am working on Martin Minka's date diff function as found
at http://forge.mysql.com/snippets/view.php?id=56. It is a beautiful
thing. However, I am trying to alter it or identify
Ok, So, What I have come up with (so far) as a variant of Baron's query:
SET @d1 = '2007-2-1 00:00:00';# Start date
SET @d2 = '2007-2-28 23:59:59';# End date
SET @tdiff = TIMEDIFF( TIME(@d1), TIME(@d2) );
SET @dow1 = DAYOFWEEK(@d1);
SET @dow2 = DAYOFWEEK(@d2);
SET @days = DATEDIFF(@d2,@d1);
SET
Ok then,
Now I have a result set so very close to where I want to be. The query
thus far:
mysql SET @d1 = '2007-02-01 18:24:04';# Start date
SET @d2 = '2007-2-28 23:05:40';# End date
set @wkldays = (select WorkDayDiff`(@d2,@d1)-1);
Set @wkldays2 = if(@wkldays 0,1,0);
set @Day_End = (select
Craig,
Right you are, here is a corrected func:
DROP FUNCTION IF EXISTS BizDateTimeDiff;
DELIMITER |
CREATE FUNCTION BizDateTimeDiff( d1 DATETIME, d2 DATETIME )
RETURNS CHAR(30)
DETERMINISTIC
BEGIN
DECLARE dow1, dow2, days, wknddays INT;
DECLARE tdiff CHAR(10);
SET dow1 = DAYOFWEEK(d1);
SET
Hello all,
I am working on Martin Minka's date diff function as found
at http://forge.mysql.com/snippets/view.php?id=56. It is a beautiful
thing. However, I am trying to alter it or identify a similar function
that instead of giving me the number of days between two dates it
returns
Craig,
I am working on Martin Minka's date diff function as found
at http://forge.mysql.com/snippets/view.php?id=56. It is a beautiful
thing. However, I am trying to alter it or identify a similar function
that instead of giving me the number of days between two dates it
returns the number of
