Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2010
    Posts
    32

    Unanswered: SQL: Increment/Calculate new value depending on the value of another field

    Hi,

    I need help in MySQL creating a new field during a query that increments each iteration depending on the value of another field. This will ultimately (hopefully) be part of a stored procedure.

    Here is how my table looks:

    Fields:
    Code:
    Ticket#... updatedon... resolvedon...
    INC00541   1/1/13       1/9/13
    INC00541   1/3/13       1/9/13
    INC00541   1/2/13       1/9/13
    INC00876   1/1/13       1/11/13
    INC00876   1/5/13       1/11/13
    Here is the result I would like to obtain. the fields in <> do not exist and values need to be created via the query.
    Code:
    Ticket#... updatedon... resolvedon... <increment>... <duration>...
    INC00541   1/1/13       1/9/13        1              1 day
    INC00541   1/2/13       1/9/13        2              5 days
    INC00541   1/7/13       1/9/13        3              2 days
    INC00876   1/1/13       1/11/13       1              4 days
    INC00876   1/5/13       1/11/13       2              6 days
    The reason for multiple rows is because tickets get reassigned to different teams to work on so separate SLAs must be calculated per reassignment.

    I basically need to number each ticket, ordered by updatedon asc, starting at 1 when a new ticket# begins.

    for Part 2, I need to calculate the duration of each reassignment. The duration is calculated based on the "next in line", or if it's the last, then it's relation to the resolved date. Perhaps there's more than 1 way to calculate this though.

    hopefully there is some voodoo that can accomplish this. We're using a tool that lacks a lot of versatility.

    thanks,
    -dq

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    How about half an answer?

    Try this to get the row number:

    Code:
    SELECT a.Ticket_Num, a.UpdatedOn, count(*) as row_Num
      FROM myTable a
      JOIN myTable  b ON a.Ticket_Num = b.Ticket_Num AND a.UpdatedOn >= b.UpdatedOn
     GROUP BY a.Ticket_Num, a.UpdatedOn
     ORDER BY a.Ticket_Num, row_Num
    As far as the 2nd part of your question. . . I'm afraid that is above my pay grade right now! Somehow the query has to look ahead 1 row at UpdatedOn to get the ending date while the ticket_Num is the same. When it changes, look at the ResolvedOn column for the ending date.

    I would be interested in that solution also!

    Too bad MySQL does not support Common Table Expressions or row_number window function. I know it would make this whole query much easier.
    Last edited by LinksUp; 06-18-13 at 12:05.

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Have a look at using session variables. Here is an example MySQL – running totals/determine row position IT Integrated Business Solutions
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Apr 2010
    Posts
    32
    LinksUp, your query worked. thanks! Embarrassingly i still don't see how. Some tickets have 4+ reassignments and it numbers 1, 2, 3, 4, then starts over with a new ticket.

    it-iss.com, thanks for the link, i'll review this page for the remainder of my tasks and how to implement this query.

  5. #5
    Join Date
    Apr 2010
    Posts
    32
    Also, I'm shocked MySQL doesn't support CTEs. It wasn't my decision to use MysQL and this was my secret weapon but now I might have to redesign a few things. thanks for the heads up

  6. #6
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by DonQuixote View Post
    Also, I'm shocked MySQL doesn't support CTEs. It wasn't my decision to use MysQL and this was my secret weapon but now I might have to redesign a few things. thanks for the heads up
    I was able to come up with a query that solves both parts of your problem without a CTE. But it relies upon the window function Row_Number. I have not been able to come up with a proper sub query that returns the needed row number. All my attempts result in a "sub query cannot return more than one value" error message.

    Just thinking out loud, what if the table had the row number as a column. . . ?

    Maybe what I have would give you an idea of a direction to head in. Do you want to see it or would it just muddy the waters?

  7. #7
    Join Date
    Apr 2010
    Posts
    32
    Quote Originally Posted by LinksUp View Post
    I was able to come up with a query that solves both parts of your problem without a CTE. But it relies upon the window function Row_Number. I have not been able to come up with a proper sub query that returns the needed row number. All my attempts result in a "sub query cannot return more than one value" error message.

    Just thinking out loud, what if the table had the row number as a column. . . ?

    Maybe what I have would give you an idea of a direction to head in. Do you want to see it or would it just muddy the waters?
    I'm not familiar with row number function. do you really just need something similar to what you wrote, but not reset after every ticket#? that I think i have. I would really like to see what you have though.
    Last edited by DonQuixote; 06-19-13 at 09:56.

  8. #8
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by DonQuixote View Post
    I'm not familiar with row number function. do you really just need something similar to what you wrote, but not reset after every ticket#? that I think i have. I would really like to see what you have though.
    There are a number of functions called Window Functions (nothing to do with Microsoft Windows) that make many queries likes this very easy. They include Row_Number, Rank, Dense_Rank, NTile, Nth_Value, First_Value, Last_Value and a few others. These are part of the ISO standard and each vendor implements whatever subset of the standard they want.

    I used my own suggestion and added RowNumber to the table and was able to populate it and get the correct days. I, obviously, don't have MySql so this solution will have to be adapted for that.

    The following scenario might work:

    1. Create a temp table
    2. Populate it with the data needed for the report or display
    3. Populate the RowNumber with an Update Statement
    4. Query for the days elpased

    1. Create a temp table adding the RowNumber column
    Code:
    create table #t1 
    (
       Ticket_Num char(8)
     , UpdatedOn Date
     , ResolvedOn Date
     , RowNumber int
     )
    2. Populate it with the data needed for the report or display
    Code:
    Insert #t1 (ID, Ticket_Num, UpdatedOn, ResolvedOn) Values
     ('INC00541',   '2013-01-01',       '2013-01-09')
    ,('INC00541',   '2013-01-02',       '2013-01-09')
    ,('INC00541',   '2013-01-07',       '2013-01-09')
    ,('INC00876',   '2013-01-01',       '2013-01-11')
    ,('INC00876',   '2013-01-05',       '2013-01-11')

    3. Populate the RowNumber with an Update Statement
    (The declaration of the variables will need to be modified for your environment. This also could be a stored procedure.)

    Code:
    DECLARE
          @iSeq int = 0,
          @iLastTicket char(8),
          @iLastDate   Date
    
       UPDATE X
          SET
             RowNumber = @iSeq,
             @iSeq = CASE
                WHEN @iLastTicket = X.Ticket_Num and @iLastDate <= X.UpdatedOn THEN @iSeq + 1
                ELSE 1 END,
             @iLastTicket = X.Ticket_Num,
             @iLastDate   = X.UpdatedOn
         FROM #t1 X
        INNER JOIN (SELECT TOP 100 PERCENT Ticket_Num, UpdatedOn
                      FROM #t1
                      ORDER BY Ticket_Num, UpdatedOn
                   ) X2 on X2.Ticket_Num = X.Ticket_Num and X2.UpdatedOn = X.UpdatedOn
    4. Query for the days elapsed
    Code:
    SELECT c.ID, c.Ticket_Num, c.UpdatedOn, c.ResolvedOn, c.RowNumber, datediff(coalesce(n.UpdatedOn, c.ResolvedOn), c.UpdatedOn,)
      FROM 
       (   Select ID, Ticket_Num, UpdatedOn, ResolvedOn, RowNumber
             From #t1
       ) as c
      left outer join 
       (   Select Ticket_Num, UpdatedOn, ResolvedOn, RowNumber
             From #t1
       ) as n
         on c.Ticket_Num = n.Ticket_Num and c.RowNumber = n.RowNumber - 1
    ORDER BY c.Ticket_Num

    With a Row_Number function, you could dispense with steps 1, 2, 3 and your query would be something like this:

    Code:
    SELECT c.ID, c.Ticket_Num, c.UpdatedOn, c.ResolvedOn, c.RowNum, datediff(coalesce(n.UpdatedOn, c.ResolvedOn), c.UpdatedOn)
      FROM 
       (   Select ID, Ticket_Num, UpdatedOn, ResolvedOn, ROW_NUMBER() OVER (Partition By Ticket_Num Order by UpdatedOn) as RowNum
             From #t1
       ) as c
      left outer join 
       (   Select Ticket_Num, UpdatedOn, ResolvedOn, ROW_NUMBER() OVER (Partition By Ticket_Num Order by UpdatedOn) as RowNum
             From #t1
       ) as n
         on c.Ticket_Num = n.Ticket_Num and c.RowNum = n.RowNum - 1
    ORDER BY c.Ticket_Num
    With a CTE, it would even be more compact and readable. You would not need the multiple Select statements for the Row_Number.

    Hopefully, some of this will help.

  9. #9
    Join Date
    Apr 2010
    Posts
    32
    Quote Originally Posted by LinksUp View Post
    stuff
    Wow, what a reply. I'm trying to work through this, but apparently MySQL doesn't support any windows functions. I tried a basic query using only a basic ROW_NUMBER() and it failed unknown function.

    In step 3, it fails at FROM, expecting a semicolon or probably a where clause. It also doesn't like the top 100 percent. Apparently MySQL lacks some severe functionality. I wish we went with a different db. I used your earlier query instead to get the row numbers working.

    Anyways, This was still enough to get it working, both part 1 and 2, but found out my date data is corrupt so i can't confirm 100% yet. I need to reinsert all of my data and see what happened. I'll let you know.

    thanks again!
    Last edited by DonQuixote; 06-21-13 at 09:27.

Posting Permissions

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