Results 1 to 6 of 6

Thread: Smarter SQL?

  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Smarter SQL?

    Hi

    I wrote the query below with the help of the gurus monitoring this forum. The script below

    Code:
    SELECT SUM(CALL_ATTEMPTS) OM_OM_CALL_ATTEMPTS, SUM(CALLS_ANSWERED) OM_OM_CALLS_ANSWERED, SUM(CALL_MINS) OM_OM_CALL_MINS
    from
    (SELECT t11.DATETIME,
      t11.call_attempts + t22.OM_OM_attempts CALL_ATTEMPTS,
      t11.calls_answered + t22.OM_OM_answered CALLS_ANSWERED,
      t11.CALLING_MINS + t22.Answered_Mins CALL_MINS
                                  
    FROM                
    
    (select T1.Datetime, T1.Dest, (T1.call_attempts - coalesce(T2.call_attempts, 0)) call_attempts,
    
    (T1.calls_answered - coalesce(T2.calls_answered, 0)) calls_answered, 
    
    (T1.Calling_MINs - coalesce(T2.Calling_MINs, 0)) Calling_MINs
    
    from
    (SELECT TRUNC(DATETIME,'HH') DATETIME,decode(OBJECT_ID,20,'OM-OM_20',225,'OM-OM_225',250,'OM-PSTN','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 ERICSSON_NSS.TRDIP_RAW
    
    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-1,'DD')+(0/24) and TRUNC(SYSDATE-1,'DD')+(0.999/24)
    
    group by TRUNC(DATETIME,'HH'), decode(OBJECT_ID,20,'OM-OM_20',225,'OM-OM_225',250,'OM-PSTN','OM-INT')
    )T1 
    
    left join 
    
    (SELECT TRUNC(DATETIME,'HH') DATETIME,decode(OBJECT_ID,20,'OM-OM_20',225,'OM-OM_225',250,'OM-PSTN','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 ERICSSON_NSS.TRDIP_RAW
    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-1,'DD')+(0/24) and TRUNC(SYSDATE-1,'DD')+(0.999/24)
    group by TRUNC(DATETIME,'HH'), decode(OBJECT_ID,20,'OM-OM_20',225,'OM-OM_225',250,'OM-PSTN','OM-INT')
    ) T2 
    
    on T2.dest = 'OM-OM_225' and T1.dest = 'OM-OM_20'
    ) t11,
    
    (Select DATETIME, SUM(OM_OM_Attempts) OM_OM_Attempts, SUM(OM_OM_Answered) OM_OM_ANSWERED,sum (Answered_Mins) Answered_Mins
    FROM
    (
    select
    
    TRUNC(DATETIME,'HH')DATETIME,MSC, 
    
    TGNO ,
    
    sum(CC_O) OM_OM_Attempts,
    
    sum(CCS_WITH_ANSWER_O) OM_OM_Answered,
    
    round(sum(TV_ANSWER_O)/60,2) Answered_Mins
    
    FROM SIEMENS_NSS.TGRP 
    
    WHERE DATETIME between TRUNC(SYSDATE-1,'DD')+(0/24) and TRUNC(SYSDATE-1,'DD')+(0.999/24)
    
    AND ( TGNO like 'INLPOG'  OR TGNO like 'MICOUT' ) and (msc like 'MSC2' or msc like 'MSC6' or msc like 'MSC11' or msc like 'MSC12' or msc like 'MSC1' or msc like 'MSC13'  )
    
    GROUP BY TRUNC(DATETIME,'HH'), MSC, TGNO)
    GROUP BY DATETIME
    ) t22
    
    WHERE t11.datetime = t22.datetime
    and t11.dest='OM-OM_20')
    uses

    Code:
    and DATETIME between TRUNC(SYSDATE-1,'DD')+(0/24) and TRUNC(SYSDATE-1,'DD')+(0.999/24)
    to return data for 00:00 to 00:59 hrs....I need to add the output of this script for all 24hrs. The way I did this was change the time to (1/24) and
    (1.999/24) and get the result, then change the hours to (3/24)....etc till 23/24 and joined all 24 scripts for OM_OM_CALL_ATTEMPTS...etc.

    I was just wondering if there was a smarter way to do this? Maybe a loop or something that increments the hours one by one and then sums up all the OM_OM_CALL_ATTEMPTS, OM_OM_CALLS_ANSWERED and OM_OM_CALL_MINS from all 24 outputs?

    NOTE: The nature of the output of the code is such that it won't allow me to specify the range 0/24 to 23/24 in the query. I have to run the query for each of the 24hrs individually.

    Thanks and Regards
    Shajju

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    This post make about as much sense as your previous posts.

    If the posted SQL produces desired results, declare victory & call it a day.

    The posted SQL is borderline useless to us without DDL for table(s) & DML for test/sample data.
    We can't run it to see what it produces.

    If it does not produce desired results then,....

    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.

    Code:
    SELECT Sum(call_attempts)  om_om_call_attempts,
           Sum(calls_answered) om_om_calls_answered,
           Sum(call_mins)      om_om_call_mins
    FROM   (SELECT t11.datetime,
                   t11.call_attempts + t22.om_om_attempts  call_attempts,
                   t11.calls_answered + t22.om_om_answered calls_answered,
                   t11.calling_mins + t22.answered_mins    call_mins
            FROM   (SELECT t1.datetime,
                           t1.dest,
                           (t1.call_attempts - Coalesce(t2.call_attempts,0))   call_attempts,
                           (t1.calls_answered - Coalesce(t2.calls_answered,0)) calls_answered,
                           (t1.calling_mins - Coalesce(t2.calling_mins,0))     calling_mins
                    FROM   (SELECT   Trunc(datetime,'HH')                       datetime,
                                     Decode(object_id,20,'OM-OM_20',
                                                      225,'OM-OM_225',
                                                      250,'OM-PSTN',
                                                      '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     ericsson_nss.trdip_raw
                            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 - 1,'DD') + (0 / 24) AND Trunc(sysdate - 1,'DD') + (0.999 / 24)
                            GROUP BY Trunc(datetime,'HH'),
                                     Decode(object_id,20,'OM-OM_20',
                                                      225,'OM-OM_225',
                                                      250,'OM-PSTN',
                                                      'OM-INT')) t1
                           LEFT JOIN (SELECT   Trunc(datetime,'HH')                       datetime,
                                               Decode(object_id,20,'OM-OM_20',
                                                                225,'OM-OM_225',
                                                                250,'OM-PSTN',
                                                                '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     ericsson_nss.trdip_raw
                                      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 - 1,'DD') + (0 / 24) AND Trunc(sysdate - 1,'DD') + (0.999 / 24)
                                      GROUP BY Trunc(datetime,'HH'),
                                               Decode(object_id,20,'OM-OM_20',
                                                                225,'OM-OM_225',
                                                                250,'OM-PSTN',
                                                                'OM-INT')) t2
                             ON t2.dest = 'OM-OM_225'
                                AND t1.dest = 'OM-OM_20') t11,
                   (SELECT   datetime,
                             Sum(om_om_attempts) om_om_attempts,
                             Sum(om_om_answered) om_om_answered,
                             Sum(answered_mins)  answered_mins
                    FROM     (SELECT   Trunc(datetime,'HH')           datetime,
                                       msc,
                                       tgno,
                                       Sum(cc_o)                      om_om_attempts,
                                       Sum(ccs_with_answer_o)         om_om_answered,
                                       Round(Sum(tv_answer_o) / 60,2) answered_mins
                              FROM     siemens_nss.tgrp
                              WHERE    datetime BETWEEN Trunc(sysdate - 1,'DD') + (0 / 24) AND Trunc(sysdate - 1,'DD') + (0.999 / 24)
                                       AND (tgno LIKE 'INLPOG'
                                             OR tgno LIKE 'MICOUT')
                                       AND (msc LIKE 'MSC2'
                                             OR msc LIKE 'MSC6'
                                             OR msc LIKE 'MSC11'
                                             OR msc LIKE 'MSC12'
                                             OR msc LIKE 'MSC1'
                                             OR msc LIKE 'MSC13')
                              GROUP BY Trunc(datetime,'HH'),
                                       msc,
                                       tgno)
                    GROUP BY datetime) t22
            WHERE  t11.datetime = t22.datetime
                   AND t11.dest = 'OM-OM_20')
    Last edited by anacedent; 09-17-09 at 22:45.
    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.

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Holy cow! No wonder you can't figure anything out. You've got like 10 levels of nested subqueries.

    I don't know why I'm bothering, because you've pretty much disregarded everything we've told you so far... Who knows, maybe you are a masochist and like doing everything the hard way.

    You really should chalk it up to a learning experience and throw the entire thing out. Honestly. Then start over by creating a base view. Ideally, your base view would be grouped by TRUNC(datetime, 'HH'). That will put all of your transactions in a 1 hour bin. Don't put any datetime items in the where clause of your view. You'll filter based on datetime later.

    Here are some other tips:
    - Use IN instead of all of those ORs
    Code:
    WHERE object_id IN (20, 150, 160, 161, 162, 163, 164, 165, 166, 167, 225, 250)
    Same goes for your likes. Don't use LIKE if you are not doing wild card.
    Code:
    WHERE msc IN ('MSC1', 'MSC2', 'MSC6', 'MSC11', 'MSC12', 'MSC13')
    Thanks for formatting his code Ana. I wouldn't have bothered with it otherwise.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Thanks for formatting his code Ana. I wouldn't have bothered with it otherwise
    I was just trying to understand this beast & wanted to share.
    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.

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju
    to return data for 00:00 to 00:59 hrs....I need to add the output of this script for all 24hrs. The way I did this was change the time to (1/24) and
    (1.999/24) and get the result, then change the hours to (3/24)....etc till 23/24 and joined all 24 scripts for OM_OM_CALL_ATTEMPTS...etc.
    Maybe this thread on AskTom could show you smarter (and precise) way for specifying a time: http://asktom.oracle.com/pls/asktom/...#2911790494626.
    In short: 0.001/24 (= 1 - 0.999/24) is not a second; 1/24/60/60 is.
    Code:
    SQL> select (0.001/24)*24*60*60 "Pseudo second",
      2         (1/24/60/60)*24*60*60 "True second"
      3  from dual;
    
    Pseudo second True second
    ------------- -----------
              3.6           1
    
    1 row selected.
    
    SQL>
    Quote Originally Posted by shajju
    I was just wondering if there was a smarter way to do this? Maybe a loop or something that increments the hours one by one and then sums up all the OM_OM_CALL_ATTEMPTS, OM_OM_CALLS_ANSWERED and OM_OM_CALL_MINS from all 24 outputs?
    If you want to incorporate it into a query, use one of http://www.orafaq.com/wiki/Oracle_Ro...tor_Techniques.

    [Edit: corrected misread expression, added an example]
    Last edited by flyboy; 09-18-09 at 04:00.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also, it sounds like you would be running this query for yesterday's data, by your mention of running this in a loop. Why not run it once for the entire day and group by the hour? Means running it once and getting 24 rows instead of running it 24 times getting 1 row.
    Dave Nance

Posting Permissions

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