1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need help with sql analytical query

Discussion in 'SQL PL/SQL' started by suhail, Jul 30, 2016.

  1. suhail

    suhail Newly Initiated

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Riyadh
    Hello,

    I have master table having information for TT_ID, Customer_Circuit_ID,Location and details table B having the following

    information:TT_ID,User_ID,Status, Newgroup.
    I need to calculate the no.of time the ticket has been referred between 2 same group consecutively for count equal to 6 or greater than 6.

    Table A is linked to Table B on TT_ID.

    For Example:
    Table A
    TT_ID--------------------Customer_Circuit_ID---Location
    1234----------------------IPMPLS----------------Abha
    4567----------------------MWVPN-----------------Khurais



    Table B
    TT_ID------------User_ID---------Status------Newgroup
    1234 ------------ SDCC_123--------Assigned----SDCC909
    1234 ------------ TNOC_456--------Referred----TNOC
    1234 ------------ ANOC_789--------Referred----ANOC
    1234 ------------ DNOC121 --------Referred----DNOC
    1234 ------------ ANOC_789--------Referred----ANOC
    1234 ------------ ENOC_146--------Referred----ENOC_DATA
    1234 ------------ ANOC_789--------Referred----ANOC
    1234 ------------ ENOC_146--------Referred----ENOC_DATA
    1234 ------------ ANOC_789--------Referred----ANOC
    1234 ------------ ENOC_146--------Referred----ENOC_DATA
    1234 ------------ ANOC_789--------Referred----ANOC
    1234 ------------ ENOC_146--------Referred----ENOC_Data
    1234 ------------ MW_9009---------Referred----MW_Mobile
    1234 ------------ MW_9009---------CLosed------MW_Mobile

    The consecutive reference or the ping pong for the above ticket between 2 group is 7
    i.e. ANOC-ENOC_Data-ANOC-ENOC_Data-ANOC-ENOC_Data-ANOC(Total: 7)


    I want the result to be displayed some thing like below
    TT_ID, Ping_Pong
    1234 7

    Scenarios 2:

    Table B
    TT_ID, User_ID, Status , Newgroup
    1234 , SDCC_123 , Assigned , SDCC909
    1234 , TNOC_456 , Referred , TNOC
    1234 , ANOC_789 , Referred , ANOC
    1234 , DNOC121 , Referred , DNOC
    1234 , ANOC_789 , Referred , ANOC
    1234 , ENOC_146 , Referred , ENOC_DATA
    1234 , ANOC_789 , Referred, ANOC
    1234 , ENOC_146 , Referred , ENOC_DATA
    1234 , ANOC_789 , Referred , ANOC
    1234 , ENOC_146 , Referred , ENOC_DATA
    1234 , ANOC_789 , Referred , ANOC
    1234 , ENOC_146 , Referred , ENOC_Data
    1234 , MW_9009 , Referred , Mobile_Transmission
    1234 , MW_9009 , Referred , MW_Mobile
    1234 , SANOC_1 , Referred , SANOC
    1234 , MW_9009 , Referred , MW_Mobile
    1234 , SANOC_1 , Referred , SANOC
    1234 , MW_9009 , Referred , MW_Mobile
    1234 , SANOC_1 , Referred , SANOC
    1234 , MW_9009 , Referred , MW_Mobile
    1234 , SANOC_1 , Referred , SANOC
    1234 , SANOC_1 , Referred , Closed

    In the above scnenario
    The ping pong will be calculated as follow:

    ANOC-ENOC_Data-ANOC-ENOC_Data-ANOC-ENOC_Data-ANOC(Total: 7)
    MW_Mobile - SANOC - MW_Mobile - SANOC - MW_Mobile - SANOC - MW_Mobile - SANOC (Total: 8)

    TT_ID, Ping Pong
    1234 -----15(7+8)
     
    Last edited: Jul 30, 2016
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    1) you need to provide the scripts of creation of your tables and scripts of filling with data
    2) you migth to write query .
    if you have problems, then provide query here and we will help you.


    1)
    Analytic Functions

    2) Analytics Function
     
  3. Silambarasan K

    Silambarasan K Active Member

    Messages:
    6
    Likes Received:
    3
    Trophy Points:
    85
    Location:
    Chennai
    SELECT COUNT(*)
    FROM (
    SELECT INL.*,
    CASE WHEN ((NEWGROUP = (LEAD(NEWGROUP,2,0) OVER(ORDER BY ROWID) ))
    OR (NEWGROUP = (LAG(NEWGROUP,2,0) OVER(ORDER BY ROWID) ))) THEN 'R' END AS IND_2
    FROM (
    SELECT NEW.*,
    CASE WHEN ((NEW.NEWGROUP = (LEAD(NEWGROUP,2,0) OVER(ORDER BY ROWID) ))
    OR (NEW.NEWGROUP = (LAG(NEWGROUP,2,0) OVER(ORDER BY ROWID) ))) THEN 'S' END AS IND ,
    LEAD(NEWGROUP,2,0) OVER(ORDER BY ROWID) AS NEXT_VALUE_1 ,
    LAG(NEWGROUP,2,0) OVER(ORDER BY ROWID) AS PREVIOUS_VALUE_1 ,
    ROWID AS R_ID

    FROM NEW
    ) INL
    WHERE INL.IND ='S'
    ) INL2
    WHERE INL2.IND_2='R'

    ---
    i think this SQL query satisfy your requirement . please join tab1 with new table . thats it you will get ping pong count ...,
     
    suhail likes this.
  4. suhail

    suhail Newly Initiated

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Riyadh

    Sir,
    Can you please let me know which column to join on between the newly created table and the tab1.
    Will this give me the count per ticket
     
    Last edited: Aug 15, 2016