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/[EMAIL PROTECTED]
