Re: [MYSQL]time of elapsed time

Hi Craig,

Weston, Craig (OFT) wrote:
> Hello everyone.
>
> Once again, I am jousting at the windmill of time and date formulae
> within MYSQL. I seek to create a cross-tab or pivot table of the SUM of
> all times with a specific category, on a per-ticket basis. I have
> everything working except the math part. Even that is kind of working
> ok, but it is not adding up The math part is:
>
>
>
> IF(`status` = 'WIP',SUM(time_to_sec(`CLOCK_TIME`)),0) AS 'WIP'
>
>
>
> (the same formula with 'CLOSED','OPEN', etc lists all the various
> statuses available.)
>
>
>
> CLOCK_TIME is a varchar field that contains a 4 digit date counter and a
> timer, in the format of
>
>
>
> 0293 23:44
>
> 0001 00:29
>
> 0001 19:15

My hunch is this is the problem. You should split the field into two: one for the date counter, one for the time. time_to_secs() is probably returning zero for most of these.

>
> Now, I run the query and get results. Every ticket has more than one
> status. But, for each ticket, I get a single line that appears to have
> the entire ticket time (in seconds) in one field and the rest are zero.
>
>
>
> I think this tells me that the statement is working but that I am
> grouping them wrong? `key` is the ticket number.
>
>
>
> From
>
> `clock_data`
>
> group by
>
> `clock_data`.`key`
>
>
>
> So my result set looks like
>
>
>
> 17881375,0,0,0,0,15235975,0,0,0,0,0,0,0,0
>
> 18184639,21362513,0,0,0,0,0,0,0,0,0,0,0,0
>
>
>
> Etc.
>
> Can anyone help me over this hill? I think I am writing the if statement
> incorrectly somehow but don't see a way out of the box yet.

-- 
MySQL General Mailing List
For list archives: 
http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/mysql?unsub=lists@pantek.com