Results 1 to 6 of 6

Thread: SQL Query

  1. #1
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Unanswered: SQL Query

    V7.2 FP9 , AIX

    select * from sn2

    TS VAL
    -------- -----------
    17:13:48 8
    17:14:03 10
    17:14:09 15

    3 record(s) selected.


    I'm looking at getting a result like :

    TSDIFF VALDIFF
    ---------------------
    15 2
    6 5

    TSDIFF 15 is 17:14:03 - 17:13:48 seconds and the corresponding VALDIFF 2 is 10-8 ...

    Can anyone help, please ?

    BTW, I have quoted a simple thing with one column(other than timestamp) ... Actually I will want to have about half-a-dozen columns like this ...


    Sathyaram
    Last edited by sathyaram_s; 09-18-03 at 13:08.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: SQL Query

    Originally posted by sathyaram_s
    V7.2 FP9 , AIX

    select * from sn2

    TS VAL
    -------- -----------
    17:13:48 8
    17:14:03 10
    17:14:09 15

    3 record(s) selected.


    I'm looking at getting a result like :

    TSDIFF VALDIFF
    ---------------------
    15 2
    6 5

    TSDIFF 15 is 17:14:03 - 17:13:48 seconds and the corresponding VALDIFF 2 is 10-8 ...

    Can anyone help, please ?

    BTW, I have quoted a simple thing with one column(other than timestamp) ... Actually I will want to have about half-a-dozen columns like this ...


    Sathyaram
    I think this could work:

    with t (ts, val, rn) as
    (select ts, val, rownumber() over (order by ts) as rn from sn2)
    select timestampdiff(2, t1.s-t2.ts) as tsdiff, t1.val - t2.val as valdiff
    from t t1, t t2 where t1.rn=t2.rn+1
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: SQL Query

    Nick .... Thank You ...


    Exactly what I wanted ...

    For the benefit of those who would like to use this :

    timestampdiff(2, char(t1.ts-t2.ts))

    instead of

    timestampdiff(2, t1.s-t2.ts)


    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Aug 2003
    Posts
    21

    Re: SQL Query

    Thats a good

    If I have an additional column in the table and I wish to group on it , how to do that .. Any ideas ?

    Using sathyaram_s's example

    TS VAL CATEGORY
    -------- ------------------------
    17:13:48 8 A
    17:14:03 10 A
    17:15:03 14 A
    17:13:48 15 B
    17:14:03 18 B
    17:15:03 25 B


    Output :

    TSDIFF VALDIFF CATEGORY
    ----------------------------------
    15 2 A
    60 4 A
    15 3 B
    60 7 B


    TIA

  5. #5
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    This would possibly a solution for adding the category to the input, based on the solution on tek-tips forum:

    thread: http://www.tek-tips.com/viewthread.c...PID=178&page=1


    SELECT TEMP.CATEGORY,TEMP.KEY, (TEMP.KEY - TEMP2.KEY) AS KEY_DIFF, (TEMP.VALUE - TEMP2.VALUE) AS VAL_DIFF FROM
    (Select
    CATEGORY,KEY,VALUE,RANK() OVER (ORDER BY CATEGORY,KEY) AS #RANK1 from TARGET.SATHYARAMS_1909) TEMP,
    (Select
    CATEGORY,KEY,VALUE,(RANK() OVER (ORDER BY CATEGORY,KEY)) + 1 AS #RANK2 from TARGET.SATHYARAMS_1909) TEMP2
    WHERE TEMP.#RANK1 = TEMP2.#RANK2 AND (TEMP.KEY - TEMP2.KEY) > 0

    This solution uses rank() instead of rownumber() and is based on an input table with integers as key (instead of time)

    Adding category to the order by does work, but there is some output to be repressed where the last record from a category is subtracted from the first record of the next category

    Hence the restriction that difference in keys must be > 0
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    That's a real cool solution ...


    Thanks

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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