Re: [MYSQL]time of elapsed time
Weston, Craig (OFT) wrote: There were 2 changes - First was IF(`status` = 'WIP',SUM(time_to_sec(RIGHT(`CLOCK_TIME`),8)),0) AS 'WIP' ( I addded the "RIGHT" limit on the string) And second was grouping by KEY,STATUS Further experimentation makes it appear that I am getting the right number of seconds for the entire string, so I am playing without the RIGHT() modifier right now. Maybe making a table with the status's on separate rows and then querying against that to make the single line?? Changes to what? Did i miss the post where you included the entire SELECT statement? (please don't top-post) brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [MYSQL]time of elapsed time
There were 2 changes - First was IF(`status` = 'WIP',SUM(time_to_sec(RIGHT(`CLOCK_TIME`),8)),0) AS 'WIP' ( I addded the "RIGHT" limit on the string) And second was grouping by KEY,STATUS Further experimentation makes it appear that I am getting the right number of seconds for the entire string, so I am playing without the RIGHT() modifier right now. Maybe making a table with the status's on separate rows and then querying against that to make the single line?? This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, October 19, 2007 12:02 PM To: mysql@lists.mysql.com Subject: Re: [MYSQL]time of elapsed time Weston, Craig (OFT) wrote: > Baron, thank you for your response. > > I did get a different result for the query: > 18184639,12544976,0,0,0,0,0,0,0,0,0,0,0,0 > > But it still is "all" the data for the ticket in the row. > > If I group by the ticket (key) number and the status, I get one line per > status with the "right" numbers. > > 18184639,240,0,0,0,0,0,0,0,0,0,0,0,0 > 18184639,0,81360,0,0,0,0,0,0,0,0,0,0,0 > 18184639,0,0,10680,0,0,0,0,0,0,0,0,0,0 > 18184639,0,0,0,0,0,0,0,0,38280,0,0,0,0 > 18184639,0,0,0,6480,0,0,0,0,0,0,0,0,0 > 18184639,0,0,0,0,0,0,0,8040,0,0,0,0,0 > 18184639,0,0,0,0,0,0,12081596,0,0,0,0,0,0 > 18184639,0,0,0,0,0,0,0,0,0,139920,0,0,0 > 18184639,0,0,0,0,16080,0,0,0,0,0,0,0,0 > > Can you think of some way to get these numbers on to one row? > What is the query used for that result? brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MYSQL]time of elapsed time
Weston, Craig (OFT) wrote: Baron, thank you for your response. I did get a different result for the query: 18184639,12544976,0,0,0,0,0,0,0,0,0,0,0,0 But it still is "all" the data for the ticket in the row. If I group by the ticket (key) number and the status, I get one line per status with the "right" numbers. 18184639,240,0,0,0,0,0,0,0,0,0,0,0,0 18184639,0,81360,0,0,0,0,0,0,0,0,0,0,0 18184639,0,0,10680,0,0,0,0,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,0,38280,0,0,0,0 18184639,0,0,0,6480,0,0,0,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,8040,0,0,0,0,0 18184639,0,0,0,0,0,0,12081596,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,0,0,139920,0,0,0 18184639,0,0,0,0,16080,0,0,0,0,0,0,0,0 Can you think of some way to get these numbers on to one row? What is the query used for that result? brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [MYSQL]time of elapsed time
Baron, thank you for your response. I did get a different result for the query: 18184639,12544976,0,0,0,0,0,0,0,0,0,0,0,0 But it still is "all" the data for the ticket in the row. If I group by the ticket (key) number and the status, I get one line per status with the "right" numbers. 18184639,240,0,0,0,0,0,0,0,0,0,0,0,0 18184639,0,81360,0,0,0,0,0,0,0,0,0,0,0 18184639,0,0,10680,0,0,0,0,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,0,38280,0,0,0,0 18184639,0,0,0,6480,0,0,0,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,8040,0,0,0,0,0 18184639,0,0,0,0,0,0,12081596,0,0,0,0,0,0 18184639,0,0,0,0,0,0,0,0,0,139920,0,0,0 18184639,0,0,0,0,16080,0,0,0,0,0,0,0,0 Can you think of some way to get these numbers on to one row? Thanks, Craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Friday, October 19, 2007 8:53 AM To: Weston, Craig (OFT) Cc: mysql@lists.mysql.com Subject: 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/[EMAIL PROTECTED]
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/[EMAIL PROTECTED]
[MYSQL]time of elapsed time
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 ... 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. Thank you. This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.
