Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    24

    Unanswered: SQL Query -- Please Help

    Table1
    QTE_ID SEQ_NO
    1435177 2
    1435177 5
    1435177 7
    1435177 8
    1435177 12
    1435177 14

    Table2
    QTE_ID SEQ_NO CMMT_CURR_AMT
    1435177 1 98500
    1435177 2 98500
    1435177 3 0
    1435177 4 98500
    1435177 5 98500
    1435177 7 98500
    1435177 8 98500
    1435177 11 59300
    1435177 12 59300
    1435177 14 59300


    The result should be

    QTE_ID SEQ_NO CMMT_CURR_AMT
    1435177 2 0 (where 0 = cmmt_curr_amt of seq 2 - cmmt_curr_amt of seq 1 from table 2)
    1435177 5 0 (where 0 = cmmt_curr_amt of seq 5 - cmmt_curr_amt of seq 4 from table 2)
    1435177 7 0 (where 0 = cmmt_curr_amt of seq 7 - cmmt_curr_amt of seq 5 from table 2)
    1435177 8 0 (where 0 = cmmt_curr_amt of seq 8 - cmmt_curr_amt of seq 7 from table 2)
    1435177 12 0 (where 0 = cmmt_curr_amt of seq 12 - cmmt_curr_amt of seq 11 from table 2)
    1435177 14 0 (where 0 = cmmt_curr_amt of seq 14 - cmmt_curr_amt of seq 12 from table 2)



    I have to get the difference of cmmt_curr_amt from the table2 from seq 14 to seq 12 for seq14 in table 1.

    Please help me in writing the query.

    Thanks in advance.

    Srinivas
    Last edited by oracle08821; 04-23-06 at 14:26.
    Thanks
    Srinivas chityala

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Lookup the LAG/LEAD analytic function as this will give you access to the previous row and thus allow you to subtract the current amount from the amount in the previous row if you order by seq_no asc.

    Alan

Posting Permissions

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