3

I have two tables (from Request Tracker 4, actually): Tickets, and Transactions.

Tickets.id == Transactions.ObjectID

but there aren't always Transactions for every Ticket.

I'm interested in the Ticket's "TimeWorked" column. This is often set on Ticket Creation, but it's also sometimes updated by a Transaction which updates "TimeWorked". I want to get the TimeWorked value as it originally was which means I think I have to backtrack through all the linked Transactions and subtract their values. Subsequently, I also want all the updated Transactions which modified that value, but I believe I've solved that problem.

Basically I want to see who set the TimeWorked value when, and what it was on ticket creation. Here's my SQL:

SELECT
Tickets.id                                                            AS Ticket,
Tickets.Subject                                                       AS Subject,
/*
SUM(Transactions.NewValue - Transactions.OldValue)                    AS TransactionTimeWorked,
Tickets.TimeWorked-SUM(Transactions.NewValue - Transactions.OldValue) AS InitialTimeWorked,
*/
Tickets.Created                                                       AS TicketCreated
FROM
    Tickets
         LEFT JOIN Transactions       ON Tickets.id=Transactions.ObjectId
            AND Transactions.Field="TimeWorked"
WHERE
    Tickets.Queue = 50
    AND Tickets.Created > "2015-06-01"
    AND Tickets.Created < "2015-09-01";

Including that first SUM line limits the number of rows returned to those Tickets which have updating Transactions. I sort of understand why, but I want all Tickets to be included, with their original create time calculated by SUMming the Transactions and subtracting, but only if they exist.

I think my WHERE clause is correct in making the JOIN values optional, but now I think I want the same on the SUM.

An example of the output I'd like:

+--------+---------+-----------------------+-------------------+---------------------+---------------------+
| Ticket | Subject | TransactionTimeWorked | InitialTimeWorked | TicketCreated       | TransactionCreated  |
+--------+---------+-----------------------+-------------------+---------------------+---------------------+
|  11234 | test01  |                  NULL |                60 | 2015-08-03 08:23:35 |                NULL |
|  11234 | test01  |                    20 |              NULL | 2015-08-03 08:23:35 | 2015-08-04 08:23:35 |
|  11234 | test01  |                    10 |              NULL | 2015-08-03 08:23:35 | 2015-08-06 15:34:35 |
|  11235 | test02  |                  NULL |                30 | 2015-07-31 12:50:37 |                NULL |
|  11236 | test03  |                  NULL |                20 | 2015-07-31 14:51:29 |                NULL |
+--------+---------+-----------------------+-------------------+---------------------+---------------------+
4
  • Sorry @ypercube, I didn't explicitly say. I suppose I'm looking for the original value, then all subsequent individual changes (the Transaction table supplies OldValue and NewValue to allow one to do the maths on that). – davis Sep 8 '15 at 9:09
  • @ypercube, yes; there's Transactions.Created; see also better example output above – davis Sep 8 '15 at 9:23
  • So, for test01, the tables have saved a row in the Tickets with 90 and 2 rows in the Transactions with (60, 80) and (80, 90) as (oldvalue, newvalue)? – ypercubeᵀᴹ Sep 8 '15 at 9:32
  • Yes @ypercube, I believe you've understood perfectly (my results are actually fabricated, but you've understood). – davis Sep 8 '15 at 11:47
1

If you need only the initial and the last (current) value of TimeWorked, you only need to add GROUP BY (ticket) to your query:

-- query 1
SELECT
  ti.id                                          AS Ticket,
  ti.Subject                                     AS Subject,
  SUM(tr.NewValue - tr.OldValue)                 AS TransactionTimeWorkedTotal,
  ti.TimeWorked - SUM(tr.NewValue - tr.OldValue) AS InitialTimeWorked,
  ti.Created                                     AS TicketCreated
FROM
    Tickets AS ti
         LEFT JOIN Transactions AS tr      ON ti.id = tr.ObjectId
            AND tr.Field = 'TimeWorked'
WHERE
    ti.Queue = 50
    AND ti.Created > '2015-06-01'
    AND ti.Created < '2015-09-01' 
GROUP BY
    ti.id,
    ti.Subject,
    ti.TimeWorked,
    ti.Created ;

This could also be accomplished with a different approach:

-- query 2
SELECT
  ti.id                                          AS Ticket,
  ti.Subject                                     AS Subject,
  COALESCE(ti.TimeWorked - tri.OldValue, 0)      AS TransactionTimeWorkedTotal,
  COALESCE(tr.OldValue, ti.TimeWorked)           AS InitialTimeWorked,
  ti.Created                                     AS TicketCreated
FROM
    Tickets AS ti
         LEFT JOIN Transactions AS tr
            ON  ti.id = tr.ObjectId
            AND tr.Field = 'TimeWorked'
            AND tr.Created = 
                ( SELECT tr1.Created               -- finds the first
                  FROM Transactions AS tr1         -- transaction (if any)
                  WHERE ti.id = tr1.ObjectId       -- for the ticket
                    AND tr1.Field = 'TimeWorked'    
                  ORDER BY tr1.Created 
                  LIMIT 1
                )
WHERE
    ti.Queue = 50
    AND ti.Created > '2015-06-01'
    AND ti.Created < '2015-09-01' ;

Both the above queries will give the initial TimeWorked and the total of the transactions TimeWorked values.


If you want all the individual changes as well - and since the Transactions table has already the old and new values - it needs something different, but not more complicated than the previous queries. using query 2 for example:

-- query 2b
SELECT
  ti.id                                      AS Ticket,
  ti.Subject                                 AS Subject,
  NULL                                       AS TransactionTimeWorked,
  COALESCE(tr.OldValue, ti.TimeWorked)       AS InitialTimeWorked,
  ti.Created                                 AS TicketCreated,
  ti.Created                                 AS TransactionCreated
FROM
    Tickets AS ti
         LEFT JOIN Transactions AS tr
            ON  ti.id = tr.ObjectId
            AND tr.Field = 'TimeWorked'
            AND tr.Created = 
                ( SELECT tr1.Created               -- finds the first
                  FROM Transactions AS tr1         -- transaction (if any)
                  WHERE ti.id = tr1.ObjectId       -- for the ticket
                    AND tr1.Field = 'TimeWorked'    
                  ORDER BY tr1.Created 
                  LIMIT 1
                )
WHERE
    ti.Queue = 50
    AND ti.Created > '2015-06-01'
    AND ti.Created < '2015-09-01' 

UNION ALL

SELECT
  ti.id                                      AS Ticket,
  ti.Subject                                 AS Subject,
  tr.NewValue - tr.OldValue                  AS TransactionTimeWorked,
  NULL                                       AS InitialTimeWorked,
  ti.Created                                 AS TicketCreated,
  tr.Created                                 AS TransactionCreated
FROM
    Tickets AS ti
         INNER JOIN Transactions AS tr           -- all the transactions
            ON  ti.id = tr.ObjectId             -- of a ticket
            AND tr.Field = 'TimeWorked'
WHERE
    ti.Queue = 50
    AND ti.Created > '2015-06-01'
    AND ti.Created < '2015-09-01' 

ORDER BY
    Ticket,
    TransactionCreated ;
2
  • Blimey @ypercube, that's a query and a half!.. Thanks.. I think I understand it, and I think it's exactly what I'm looking for (did you have a typo on the 2nd ti.Created?). Are you using the inner SubSelect as a way of simply grabbing the first OldValue for TimeWorked? I /think/ that's what you're doing... – davis Sep 8 '15 at 14:01
  • No, there is no typo (as far as I know and if you get no errors when running. The TransactionCreated was added only for having the results ordered in some useful order.) Yes, the complicated subquery is to get the first transaction of a ticket, which will hopefully have (in OldValue) the initial TimeWorked of the ticket. – ypercubeᵀᴹ Sep 8 '15 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.