Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Joining tables...

    Hi

    This is a great forum...watched by some proper clued up people.

    I have to join 2 tables based on specific rows. But first need to do some calc in one table:
    Code:
    SELECT TRUNC(DATETIME,'HH'),decode(OBJECT_ID,20,'OM20',225,'OM225',250,'OMP','OM-INT') DEST,
    
    sum(B_ANSWERS) as calls_answered,
    
    SUM(BANSW_ATTEMPTS+ REJECTED_CALLS) as CALL_ATTEMPTS,
    round(SUM((ACCU_TRAFFIC_LVL/18)*15),2) as Calling_MINs
    
    FROM SCHEMA.TABLE A
    
    WHERE ((OBJECT_ID = 20) or  (OBJECT_ID = 225)  or (OBJECT_ID = 250) or (OBJECT_ID = 150) or (OBJECT_ID =160) or (OBJECT_ID = 161) or (OBJECT_ID = 162)  or (OBJECT_ID = 163) or (OBJECT_ID = 164) or (OBJECT_ID = 165) or(OBJECT_ID = 166)  or (OBJECT_ID = 167) )
    and DATETIME between trunc(sysdate,'hh')-1/24 and trunc(sysdate,'hh')-1/24/3600
    group by TRUNC(DATETIME,'HH'), decode(OBJECT_ID,20,'OM20',225,'OM225',250,'OMP','OM-INT')
    order by 1 desc
    OUTPUT

    DATETIME DEST CA_ANS CA_ATT CA_MIN
    09/06/2009 09:00:00 OM-INT 8553 52977 77,239.17
    09/06/2009 09:00:00 OMP 7329 18266 12,381.67
    09/06/2009 09:00:00 OM20 103815 216622 158,835.83
    09/06/2009 09:00:00 OM225 28652 76220 49,347.50

    I need to subtract OM225 from OM20 and then add the result to another table. How can I subtract? Not sure how to use the LAG function.

    The other table contains 3 columns so i can do a simple join (as I learned on this forum) but how can I join based on a particular row in the DEST column?
    Last edited by shajju; 09-06-09 at 04:29.

  2. #2
    Join Date
    Aug 2008
    Posts
    464
    Please ignore my last question about joining on a particular row in the DEST column. But would appreciate if someone could help me use the LAG function or any other for that matter to get the difference of OM20 and OM225.

    Thanks.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post DDL for tables.
    Post DML for test data.

    Post expected/desired results
    Post detailed explanation how & why test data gets transformed into expected/desired results.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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