I am declaring myself the MySQL king!
I was given a database schema and told to extract information to create a report… You can see the Schema here. Currently our database has 1,100 entries, with over 14,000 transactions. It was a pain in the ass to figure it all out, but I got it working! I rule at SQL statements. 🙂
CREATE TEMPORARY TABLE temp_status SELECT ObjectId, Content FROM ObjectCustomFieldValues ocfv WHERE Content IN (‘Approved’,’Pending’,’Required’,’Denied’) GROUP BY ObjectID ORDER BY Created DESC;
CREATE TEMPORARY TABLE temp_creator SELECT u.ID as UserID, t.ID as TicketID, u.RealName as CreatorName FROM Users u INNER JOIN Tickets t ON t.Creator = u.ID;
SELECT ocfv.ObjectId as ID, t.Subject as Subject, t.Status as Status, u.RealName as Owner, t.starts as Start, t.due as Due, t.TimeEstimated as Time, a.Content as Message, c.CreatorName as CreatorName
FROM temp_status ocfv
INNER JOIN Tickets t ON t.EffectiveID = ocfv.ObjectId
INNER JOIN Users u ON u.id = t.owner
INNER JOIN temp_creator c ON t.EffectiveID = c.TicketID
INNER JOIN Transactions trans ON t.id = trans.ObjectId
INNER JOIN Attachments a ON a.TransactionId = trans.id
WHERE ocfv.Content = “$field” AND trans.Type = “Create”
AND t.Status IN (‘new’,’open’);
It’s not overly compicated (I’ve done worse), but it was difficult because I didn’t understand the structure. For example, based on the schema, I thought that each Transaction should have a corresponding Attachment, but that’s not the case. An entry in the transacation table can actually have a corresponding entry in the ObjectCustomFieldValues (Or others) table. Which happened to be the reference I was looking for. So Yeah, I’m glad this project is over now.
Nothing else going on in my life. I keep day dreaming about things. I’m not going to talk about them here.
I have to work on Saturday, a full 8 hours. I’m kinda pissed because I was planning on going to the Arboretum this weekend, but whatever. So they don’t want to me work over 40 hours either, so today I’m working 5 and tomorrow I’m workiing 3, and then 8 on Saturday.
This weekend will also be full of cleaning. My apartment is a mess. I also have to buy food (Current contents of fridge: OJ), and get my hair cut, it’s a mess!
The guy e-mailed me again about the laptop thing. He said he got sent to Tenn for work on Saturday unexpectedly so that’s why he didn’t get back to me… Still annoying, it would have taken him 1 minute to call me and explain that to me. Fucker. I’m debating as to if I want to deal with him or not now. I mean, what if I fucking back up and reformat again, just for him to drop off the face of the earth again.
Though I REALLY want that 15″. Grr! So many problems.
Oh well.
The thought of sex lately has been repulsing.
Night all.
so lets be honest, i dont understand ANY of your work news, but it looks like codes of some sort? good work…!
BUT a 15″ anything is hott… especially if its a computer monitor.