Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2010
    Posts
    6

    Unanswered: Date difference between rows of records

    ID | STATUS | DATE | SYSTEMTIME
    --------------------------------------------------------------------
    ABC | S | 2010-01-14 | 2010-01-15-00.03.07.205829
    ABC | E | 2010-01-14 | 2010-01-15-00.16.16.473507
    ABC | S | 2010-01-15 | 2010-01-15-22.52.54.072351
    ABC | E | 2010-01-15 | 2010-01-15-23.04.34.257471

    Hi, I would like to ask, is it possible for SQL in DB2 to calculate the above test data to something like below?

    PROGID | DATE | Duration (hh:mm:ss)
    -------------------------------------------------------
    ABC | 2010-01-14 | 00:13:09
    ABC | 2010-01-15 | 00:11:40

    Note: S - Start; E - End.
    Last edited by ngaisteve; 07-27-10 at 01:29.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Yes, it is possible.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you would have to join the table to itself in a subquery getting the max SYSTEMTIME of the E status that is less than the SYSTEMTIME of your S status.
    Dave

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can do a self-join and then calculate the difference:
    Code:
    SELECT t1.progid, t1.date, t2.systemtime - t1.systemtime
    FROM   ... AS t1 JOIN ... AS t2 ON ( t1.progid = t2.progid AND t1.date = t2.date )
    WHERE  t1.status = 'S' AND
           t2.status = 'E'
    The basic idea is to get the start and end timestamps into a single row so that you can process that row in the SELECT list and/or WHERE clause.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another way may be like this:

    Code:
    SELECT id AS progid , date
         , /* calculate a difference of
                MAX(CASE status WHEN 'E' THEN systemtime END)
                and
                MAX(CASE status WHEN 'S' THEN systemtime END)
              then convert it to data type TIME.
           */ AS duration
      FROM test_data
     GROUP BY
           id , date
    ;

  6. #6
    Join Date
    Jul 2010
    Posts
    6
    okay, thanks a lot. I will try them out.

  7. #7
    Join Date
    Jul 2010
    Posts
    6
    Quote Originally Posted by stolze View Post
    You can do a self-join and then calculate the difference:
    Code:
    SELECT t1.progid, t1.date, t2.systemtime - t1.systemtime
    FROM   ... AS t1 JOIN ... AS t2 ON ( t1.progid = t2.progid AND t1.date = t2.date )
    WHERE  t1.status = 'S' AND
           t2.status = 'E'
    The basic idea is to get the start and end timestamps into a single row so that you can process that row in the SELECT list and/or WHERE clause.
    It works. Thanks a lot.

  8. #8
    Join Date
    Jul 2010
    Posts
    6
    To be more accurate, change t2.systemtime - t1.systemtime to
    timestampdiff(2, char(timestamp(to_char(timestamp(t2.systemtime), 'yyyy-mm-dd hh24:mi:ss')) - timestamp(to_char(timestamp(t1.systemtime), 'yyyy-mm-dd hh24:mi:ss')) ))

Posting Permissions

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