I hate leaving a job, you always lose so much of your work from the past. IE, here it is 2 years after leaving my last job, and I’m suddenly wishing I had backed up a lot more then I did when I left. I took all of my home directories off all the servers, and most of the root directories, which contained tons of scripts that I’d written during that time. But now I’m really wishing that I had taken some of the /etc/ config files. 🙁 Thankfully, they have been very good at fulfilling my requests for data.
I worked 12 hour on Monday, 14 hours on Tuesday, 10 yesterday, 7 today and 5.5 tomorrow, and that was just for my real job… It’ll be nice to be back to a normal schedule after this week. 🙂 The training we had for my software yesterday went off very well, only one thing blew up during the training, and that wasn’t a part of _my_ software. And the food was excellent. I love lunch meetings!
I do have a nice list of things to do still for this project, mostly annoying little things, one however is really stumping me.
You see, we’ve got this database… One table holds a transaction listing. For each “Ticket”, you can have multiple transacations, and each transaction has a type. Anyways, I need to somehow find all the transacations where a ticket goes from one state (open/new/resolved) to another state (pendind_c, pending_r). So when you change the status it creates a record like this:
OldValue: $OldValue
NewValue: $NewValue
I can get the list of them with this query: SELECT * FROM Transactions WHERE Type = ‘Status’ AND (OldValue LIKE ‘pend%’ OR NewValue LIKE ‘pend%’) AND ObjectID = $TicketID ORDER BY Created
And they all come out in the right order, but now the trick is that I need to figure out how to calculate the time that a Ticket spends in the “pend_*” state. So when a ticket changes from an “Open” state to a “Pend” state, everything from that point to when it changes back to “Open” counts against us.
Right now, I’m just (from another table) UNIXTIME(Start) – UNIXTIME(Resolved) to get the total time.. But I gotta subtract out the time spent pending.
I’m sure there’s some easy way that I’m just not thinking about, but I’m lost. It’s time for me to go home now.
Adios.
Join the table back on itself to get the old and new rows as columns next to each other. From there, you can calculate the difference as a new return value in the same query.
I may not understand the data and requirements exactly, but this query will give you a table with the OldValue, NewValue, and the difference between the two when they both have the same ObjectID and both have a status like ‘pend%.’
Unfortunately, if a ticket went back and forth multiple times, it could start to look goofy.
Hope that helps, if not, send me some data and some more stringent reqs/expected output. 🙂