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.

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.

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:baron@xaprb.com]
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.

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

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: mysql@subtropolix.org [mailto:mysql@subtropolix.org]
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/mysql?unsub=c...ft.state.ny.us

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