Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2012
    Posts
    9

    Unhappy Unanswered: Earliest date query help

    This is for a very, very basic ticketing system in which there are two tables

    ticket(id, problem, status, priority, loggedtime, customerID, productID)
    ticketupdate(id, message, updatetime, ticketid, staffid)

    (I think the field types are self-explanatory?) Status can be either open, or closed, and priority ranges from 1-3. ticketupdate.ticketid = ticket.id. loggedtime and updatetime are timestamps.

    I want to produce a query which, for only closed tickets, produces an output similar to this:

    -------------------------------------------------------------------------------------
    | ticketid | number of updates | open to first update | open to last update |
    -------------------------------------------------------------------------------------

    Number of updates refers to how many instances of the same ticketid there are in ticketupdate.
    Open to first update refers to the time between the ticket being opened (ticket.loggedtime) and the first update before sent from a staff member (ticketupdate.updatetime).
    Open to last update refers to the time between the ticket being opened (ticket.loggedtime) and the last update sent form a staff member (ticketupdate.updatetime).

    So far I have the following query:

    Code:
    -- Counts how many ticketupdate responses there are to id1
    Select count (ticket.id)
    FROM ticket, ticketupdate
    WHERE ticket.id=ticketupdate.ticketid
    AND ticket.id=1;
    But how do I go about doing the rest? I know min(updatetime) and max(updatetime) has to be used somewhere to get the most recent and earliest updatetimes, but apart from that, I'm stuck on how to join it all together.

    Sorry if anything is unclear ><

  2. #2
    Join Date
    May 2008
    Posts
    277
    I haven't actually run this to check for errors, but try this:

    Code:
    select
        ticket.id,
        summary.update_count,
        ticket.loggedtime - summary.first_update as first_response_interval,
        ticket.loggedtime - summary.last_update as resolution_interval
    from
        ticket
        inner join (
            select
                id,
                count(*) as update_count,
                min(updatetime) as first_update,
                max(updatetime) as last_update
            from ticket
            group by id
        ) as summary
            using (id)
    where ticket.status = 'closed'

  3. #3
    Join Date
    Apr 2012
    Posts
    9
    Many thanks for your help futurity!

    Unfortunately, neither the number of updates, nor first response and resolution response works. It seems the query always takes the min(updatetime), and ignores the max altogether. Even splitting it into two separate joins doesn't help resolve the problem (though I'm not sure if that would help anyway).



    Okay, through trying a few things by myself, I've managed to get the times working, but only if I explicitly specify a ticket.id. I can't get the number of responses to work though...

    Current code:

    Code:
    select distinct
        ticket.id,  
        min(summary.update) - ticket.loggedtime as first_response_interval,
        max(summary.update) - ticket.loggedtime as resolution_interval
    from
        ticket, ticketupdate
        inner join (
            select distinct
                ticketupdate.id,
                updatetime as update
            from ticketupdate, ticket
            WHERE ticket.id = ticketupdate.ticketid
            AND ticket.id = 1
            group by ticketupdate.id, ticketupdate.updatetime
        ) as summary
    using (id)
    where ticket.status = 'closed'
    AND ticket.id = ticketupdate.ticketid
    group by ticket.id, ticket.loggedtime
    ;
    Last edited by sqln00; 04-11-12 at 16:31.

  4. #4
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by sqln00 View Post
    Unfortunately, neither the number of updates, nor first response and resolution response works. It seems the query always takes the min(updatetime), and ignores the max altogether.
    Unless I'm seriously mis-understanding something here, my query should work. I do see that I used the wrong table for the "summary" subquery. Did you fix the table so it's ticketupdate instead of ticket?

    Here's a (hopefully) fixed query:
    Code:
    select
        ticket.id,
        summary.update_count,
        ticket.loggedtime - summary.first_update as first_response_interval,
        ticket.loggedtime - summary.last_update as ticket_resolved_interval
    from
        ticket
        inner join (
            select
                ticketid,
                count(*) as update_count,
                min(updatetime) as first_update,
                max(updatetime) as last_update
            from ticketupdate
            group by ticketid
        ) as summary
            on ticket.id = summary.ticketid
    where ticket.status = 'closed'
    Last edited by futurity; 04-11-12 at 17:02. Reason: Added fixed query

  5. #5
    Join Date
    Apr 2012
    Posts
    9
    AH! I hadn't noticed that, and was busy butchering things together - haha. Thank you ever so much for your help!

    Would you mind terribly if I threw other problems I may come across, at you?

  6. #6
    Join Date
    May 2008
    Posts
    277
    Just post them here on the forum. If your questions are SQL-related and not really specific to PostgreSQL, then the ANSI SQL forum is probably better than here.

  7. #7
    Join Date
    Apr 2012
    Posts
    9
    Ah okay! Thank you!

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by futurity View Post
    Unless I'm seriously mis-understanding something here, my query should work. I do see that I used the wrong table for the "summary" subquery. Did you fix the table so it's ticketupdate instead of ticket?

    Here's a (hopefully) fixed query:
    Code:
    select
        ticket.id,
        summary.update_count,
        ticket.loggedtime - summary.first_update as first_response_interval,
        ticket.loggedtime - summary.last_update as ticket_resolved_interval
    from
        ticket
        inner join (
            select
                ticketid,
                count(*) as update_count,
                min(updatetime) as first_update,
                max(updatetime) as last_update
            from ticketupdate
            group by ticketid
        ) as summary
            on ticket.id = summary.ticketid
    where ticket.status = 'closed'
    If number of selected ticket was rather smaller than total number of rows in ticket table,
    many unused groups would be calculated in summary subquery.

    Although correlated subquery might be inefficient than normal(un-correlated) subquery,
    correlated subquery might be worth to try in this case, like...

    (Not tested)
    Code:
    SELECT
           t.id           AS ticketid
         , s.update_count AS number_of_updates
         , t.loggedtime - s.first_update AS open to first update
         , t.loggedtime - s.last_update  AS open to last update
     FROM
           ticket AS t
     CROSS JOIN
           LATERAL
          (SELECT COUNT(*)        AS update_count
                , MIN(updatetime) AS first_update
                , MAX(updatetime) AS last_update
            FROM  ticketupdate AS u
            WHERE u.id = t.id
          ) AS s
     WHERE
           t.status = 'closed'
    Last edited by tonkuma; 04-12-12 at 06:10. Reason: Add "(Not tested)"

  9. #9
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by tonkuma View Post
    If number of selected ticket was rather smaller than total number of rows in ticket table,many unused groups would be calculated in summary subquery.
    If this is an issue, why not just pull the WHERE condition inside the subquery?

    Code:
    select
        ticket.id,
        summary.update_count,
        ticket.loggedtime - summary.first_update as first_response_interval,
        ticket.loggedtime - summary.last_update as ticket_resolved_interval
    from
        ticket
        inner join (
            select
                ticketid,
                count(*) as update_count,
                min(updatetime) as first_update,
                max(updatetime) as last_update
            from ticketupdate
            where exists (
                select 1
                from ticket as t
                where (t.id, t.status) = (ticketid, 'closed')
            )
            group by ticketid
        ) as summary
            on ticket.id = summary.ticketid
    Anyway, I'm no expert at PostgreSQL's internals, but it seems that its query planner can usually figure this out as-is.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My thought was...

    Generally speaking, access more tables (usually, join them some ways or sometiome union them) would degrade performance.
    Self join or multiple reference of a table should be considered
    each reference would be different table access(then add another table to be accessed)
    from the point of performance.

    So, I tired to reference the "ticket" table once.


    This behavior must be different from product by product in details.
    But, I felt that this consideration(reduce number of referenced tables) applicable for most (relational) database systems,
    to build efficient query.
    (Even for file access)

  11. #11
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by tonkuma View Post
    Generally speaking, access more tables (usually, join them some ways or sometiome union them) would degrade performance.
    No, not true (at least not "generally").
    Because the JOIN can reduce the work that needs to be done by the database (especially if proper FK constraints are in place).

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by shammat View Post
    No, not true (at least not "generally").
    Because the JOIN can reduce the work that needs to be done by the database (especially if proper FK constraints are in place).
    I want to show same kind of your declarations, in my query.

    If you considerd multiple queries related by each other in host language,
    it's the mess.
    (I'm sorry! I learned some dirty words recently, on the web.)


    Join tables >>(far better than) Stored procedure(or any host language) using cursors/loops/temporary table(s), so on...

    Stored procedure(or any host language) using cursors/loops/temporary table(s) to combine/navigate data in a table, another table, and more ...
    << a select statement using joines of multiple tables and sometimes multiple references for a table
    < a select statement using reduced number of references for tables
    by using some SQL specific techniques(join/correlated subquery/OLAP functions/so on...) if it was possible.(at least worth to try)
    Last edited by tonkuma; 04-12-12 at 19:50. Reason: Add temporary table(s) to mess Stored procedure.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •