1 2 Previous Next 16 Replies Latest reply: Feb 27, 2014 12:35 PM by AnnPricks E RSS

Parent and sharer plan analsyis- pl/sql

2776ebc7-f291-485a-acd2-66d5750db4b6 Newbie
Currently Being Moderated

have a dataset where there are parent and child products.

Each parent has got a unique code(P1,p2 etc) and each child of the parent has got a seperate code (C1) but they share the same account no.

I want to count the parent and children with their plan change and new connections...

Here is the ddl with the desired output.

 

 

Here is my master table

CREATE TABLE MasterTable
(`Date` datetime, `Plan_code` varchar(2), `Account_no` varchar(3), `Phone_no` int, `New_connection` int, `Plan_change` int)
;

INSERT INTO MasterTable
(`Date`, `Plan_code`, `Account_no`, `Phone_no`, `New_connection`, `Plan_change`)
VALUES
('2014-02-01 13:00:00', 'P1', 'Aq', 22111, 1, 0),
('2014-02-01 13:00:00', 'C1', 'Aq', 22333, 0, 1),
('2014-02-01 13:00:00', 'C1', 'Aq', 87643, 0, 1),
    ('2014-02-01 13:00:00', 'C1', 'Av',8764543,0,1),
('2014-02-04 13:00:00', 'P2', 'Af', 6544, 0, 1),
('2014-02-04 13:00:00', 'C1', 'Af', 7663, 1, 0),
('2014-02-05 12:00:00', 'P1', 'Ab', 7543, 1, 0),
('2014-02-06 12:00:00', 'C1', 'Ab', 4333, 0, 1),
('2014-03-02 13:00:00', 'P1', 'Aq', 22111, 0, 0),
('2014-03-03 13:00:00', 'C1', 'Aq', 22333, 0, 1),
    ('2014-03-03 13:00:00', 'C1', 'VVV',4322, 1, 1),
('2014-03-04 13:00:00', 'C1', 'Aq', 87643, 0, 1),
('2014-03-05 13:00:00', 'C1', 'Aq', 87643433, 0, 1),
('2014-03-03 13:00:00', 'P2', 'Af', 6544, 0, 1),
('2014-03-03 13:00:00', 'C1', 'Af', 7663, 0, 0),
('2014-03-05 12:00:00', 'P1', 'Abf', 7543, 1, 0),
('2014-03-05 12:00:00', 'C1', 'Abf', 4333, 0, 1)
;

 

 

Here is the output table:

CREATE TABLE Output

(`Mth_Yr` varchar(5), `Parent_code` varchar(2), `Parent_count` int, `Sharer_code` varchar(2), `Sharer_count` int, `Parent_new_connect` int, `Parent_plan_change` int, `Sharer_new_connect` int, `Sharer_plan_change` int)

;

 

INSERT INTO Output

(`Mth_Yr`, `Parent_code`, `Parent_count`, `Sharer_code`, `Sharer_count`, `Parent_new_connect`, `Parent_plan_change`, `Sharer_new_connect`, `Sharer_plan_change`)

VALUES

('02-14', 'P1', 2, 'C1', 3, 2, 0, 0, 3),

('02-14', 'P2', 1, 'C1', 1, 0, 1, 1, 0),

('03-14', 'P1', 2, 'C1', 4, 1, 0, 1, 3),

('03-14', 'P2', 1, 'C1', 1, 0, 1, 0, 0)

;

Hope this helps

  • 1. Re: Parent and sharer plan analsyis- pl/sql
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    I'd like to help but, sorry, I don't understand the problem.

     

    What does each row and column of output represent?  Explain how you get the results you want from the sample data.  If some columns aren't needed for this problem, don't post them.

    Post CREATE TABLE and INSERT statements for MasterTable that work in Oracle.

    Always say which version of Oracle you're using.

    See the forum FAQ: https://forums.oracle.com/message/9362002

  • 2. Re: Parent and sharer plan analsyis- pl/sql
    2776ebc7-f291-485a-acd2-66d5750db4b6 Newbie
    Currently Being Moderated

    Hi Frank

    Thanks for your reply . And apologies for not being clear on my requirement.

    I think I mixed two requiements in 1 query.

    I am using PL/SQL Developer Version 10.0.4.1708 .

    I will re-iterate my problem.Pls ignore the above ddl:

     

    The basic scenario for this report is:

    MASTER table has a list of different line nos or (phone nos) who all have a unique account number and phone number.

    We also  have a base table in our database which is a daily snapshot to tell us what nos are active on a particular plan on a given day.

    So for example lets say on 12th Feb 20 phone nos are active as per base table.

     

    Now along with the base table, I have a MASTER Table which tells us how these phone nos arrived on base was it a new connection or was it a plan change from some other plan.

     

    And to explain the parent - child relationship, there is a leader plan (with a leader/parent code) and one phone number contains this parent code and this parent can have multiple child phone nos who all share the same plan and the way to determine the parent and child nos is through ACCOUNT NO as both parent and children share the same account no for a given shared plan.

    There are in total 4 parent plans (4 different parent codes) in my database and 1 shared plan code.

    In my query i need to count how many sharers belong to a particular parent in a given month and also have to count -out of total parent nos, how many were plan changes,how many were new connections and same for sharers as well.

     

    I can count the parent/child count in my base table which works fine .

     

     

    Here is the modified ddl for MASTER Table using sqlfiddle.com and here is the link to it. http://sqlfiddle.com/#!2/83843/1

     


    CREATE TABLE Master_Table
    (`BaseDate` datetime, `Account_no` varchar(4), `Phone_no` int, `Code` varchar(2),`New_connect` int,`Plan_change` int )
    ;

    INSERT INTO Master_Table
    (`BaseDate`, `Account_no`, `Phone_no`, `Code`,`New_connect`,`Plan_change`)
    VALUES
    ('2014-02-01 13:00:00', 'A123', 21332, 'P1',1,0),
    ('2014-02-01 13:00:00', 'A123', 21444, 'C1',0,1),
    ('2014-02-01 13:00:00', 'A123', 21445, 'C1',1,0),
    ('2014-02-01 13:00:00', 'A124', 21333, 'P2',1,0),
    ('2014-02-01 13:00:00', 'A124', 21771, 'C1',0,1),
    ('2014-02-01 13:00:00', 'A124', 21772, 'C1',0,1),
    ('2014-02-01 13:00:00', 'A124', 21773, 'C1',1,0),
    ('2014-02-01 13:00:00', 'A126', 213321,'P1',1,0),
    ('2014-02-01 13:00:00', 'A126', 214443,'C1',0,1),
    ('2014-02-01 13:00:00', 'A126', 214452,'C1',1,0),
    ('2014-02-01 13:00:00', 'A129', 213331,'P3',1,0),
    ('2014-02-01 13:00:00', 'A129', 21771, 'C1',0,1),
    ('2014-02-01 13:00:00', 'A129', 21772, 'C1',0,1),
    ('2014-02-01 13:00:00', 'A129', 21773, 'C1',1,0)

    ;

     

    My desired output is:

    Base_Date Parent_code Parent_new_connect Parent_plan_change Child_new_connect Child_plan_change

    '2014-02-01 13:00:00' P1 2 0 2 2

    '2014-02-01 13:00:00' P2 1 0 2 2

    '2014-02-01 13:00:00' P3 1 0 1 2

     

    Hope its clearer this time.

     

    Thanks

  • 3. Re: Parent and sharer plan analsyis- pl/sql
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Sorry, it's still not clear.

    2776ebc7-f291-485a-acd2-66d5750db4b6 wrote:

     

    Hi Frank

    Thanks for your reply . And apologies for not being clear on my requirement.

    I think I mixed two requiements in 1 query.

    I am using PL/SQL Developer Version 10.0.4.1708 ...

     

     

     

    Your front end and its version (e.g. SQL Developer 10.0.4.1708) could be useful information, but what's really important is your back end (i.e. database) version.  If you run this query:

    SELECT  *

    FROM    v$version;

    the cluster of numbers near the end of the first line of output is your database version. Post the complete version number (e.g. 11.2.0.2.0).

     

    ...  Now along with the base table, I have a MASTER Table  ...

    I see the CREATE TABLE and INSERT statements for the MASTER table, but not for the BASE table.

    Here is the modified ddl for MASTER Table using sqlfiddle.com and here is the link to it. http://sqlfiddle.com/#!2/83843/1

     


    CREATE TABLE Master_Table
    (`BaseDate` datetime, `Account_no` varchar(4), `Phone_no` int, `Code` varchar(2),`New_connect` int,`Plan_change` int )
    ;

     

    INSERT INTO Master_Table
    (`BaseDate`, `Account_no`, `Phone_no`, `Code`,`New_connect`,`Plan_change`)
    VALUES
    ('2014-02-01 13:00:00', 'A123', 21332, 'P1',1,0),

    ...

    Did you test those statements in Oracle before posting them?  There is no DATETIME data type in Oracle.  You can't have grave accents in column names.  (Actually, it is possible, but let's no get into that.)  Instead of

    (`BaseDate` datetime,

    do something like

    ( BaseDate   date,

    Don't use a string (such as '2014-02-01 13:00:00') in a place where a DATE is expected.  Use TO_DATE to convert strings into DATEs.

    It looks like BaseDate always has the same value in your sample data.  Is that really the best test of what you need to do?  Wouldn't it be better to have a couple of different values in your test data?

     

    My desired output is:

    Base_Date Parent_code Parent_new_connect Parent_plan_change Child_new_connect Child_plan_change

    ...

    '2014-02-01 13:00:00' P2 1 0 2 2

    ...

    Is that right?   If so, explain how you get 2 (not 1) for child_new_connect, given the sample data.

     

    Is it possible to have 2 (or more) distinct values of code that start with 'P' related to the same account_no?  If so, include an example in your sample data and results.

     

    Does phone_no play any role in this problem  If so, what?

  • 4. Re: Parent and sharer plan analsyis- pl/sql
    2776ebc7-f291-485a-acd2-66d5750db4b6 Newbie
    Currently Being Moderated

    Hi Frank

    Sorry its 11.2.0.3.0

    The reason I use sqlfiddle is because its easy to create test data and ddl.

    '2014-02-01 13:00:00' P2 1 0 2 2

    You are right it shd be 1 not 2.sorry

    Phone nos are shown here to show the distinct sharers and parents.No other reason .

     

    "Is it possible to have 2 (or more) distinct values of code that start with 'P' related to the same account_no?  If so, include an example in your sample data and results."

     

    No there is only one parent(P) code for each account.

    "It looks like BaseDate always has the same value in your sample data.  Is that really the best test of what you need to do?  Wouldn't it be better to have a couple of different values in your test data?"

     

    Here is the updated ddl to show more dates.

     

    CREATE TABLE Master_Table

    (`BaseDate` datetime, `Account_no` varchar(4), `Phone_no` int, `Code` varchar(2),`New_connect` int,`Plan_change` int )

    ;

     

    INSERT INTO Master_Table

    (`BaseDate`, `Account_no`, `Phone_no`, `Code`,`New_connect`,`Plan_change`)

    VALUES

    ('2014-02-01 13:00:00', 'A123', 21332, 'P1',1,0),

    ('2014-02-01 13:00:00', 'A123', 21444, 'C1',0,1),

    ('2014-02-01 13:00:00', 'A123', 21445, 'C1',1,0),

    ('2014-02-01 13:00:00', 'A124', 21333, 'P2',1,0),

    ('2014-02-01 13:00:00', 'A124', 21771, 'C1',0,1),

    ('2014-02-01 13:00:00', 'A124', 21772, 'C1',0,1),

    ('2014-02-01 13:00:00', 'A124', 21773, 'C1',1,0),

        ('2014-02-01 13:00:00', 'A126', 213321,'P1',1,0),

    ('2014-02-01 13:00:00', 'A126', 214443,'C1',0,1),

    ('2014-02-01 13:00:00', 'A126', 214452,'C1',1,0),

    ('2014-02-01 13:00:00', 'A129', 213331,'P3',1,0),

    ('2014-02-01 13:00:00', 'A129', 21771, 'C1',0,1),

    ('2014-02-01 13:00:00', 'A129', 21772, 'C1',0,1),

    ('2014-02-01 13:00:00', 'A129', 21773, 'C1',1,0),

        ('2014-02-02 13:00:00', 'B123', 2133222,'P1',1,0),

        ('2014-02-02 13:00:00', 'B123', 2133222,'C1',1,0),

        ('2014-02-02 13:00:00', 'B123', 2133229,'C1',0,1),

        ('2014-02-02 13:00:00', 'H123', 21654,'P2',0,1),

        ('2014-02-02 13:00:00', 'H123', 21875,'C1',0,1),

        ('2014-02-03 13:00:00', 'V123', 2133222,'P3',0,1),

        ('2014-02-03 13:00:00', 'V123', 2133212,'C1',1,0),

        ('2014-02-07 13:00:00', 'Z123', 2131234,'P2',0,1),

        ('2014-02-07 13:00:00', 'Z123', 213123433,'C1',1,0)

    ;

     

    I just wanted to mention the base table to put more context to this problem.

    Hope this helps.

  • 5. Re: Parent and sharer plan analsyis- pl/sql
    AnnPricks E Guru
    Currently Being Moderated

    Hi..  For first set of data you showed one result for C1 sharer_palnchange is "3".  But that is not 3 it should be "4" . See below datas for that.

    2014-03-02 13:00:00    P1    Aq    22111    0    0

    2014-03-05 12:00:00    P1    Abf    7543    1    0

    2014-03-03 13:00:00    C1    Aq    22333    0    1

    2014-03-04 13:00:00    C1    Aq    87643    0    1

    2014-03-05 12:00:00    C1    Abf    4333    0    1

    2014-03-05 13:00:00    C1    Aq    87643433    0    1

    INSERT INTO output_table

    SELECT TO_CHAR(date_1,'MM-YY') "Mth_Yr",

           plan_code1 "Parent_code",

           COUNT(DISTINCT date_1||account_no1||plan_code1) "Parent_count",

           plan_code2 "Sharer_code",

           COUNT(plan_code2) "Sharer_count",

           SUM(CASE WHEN (cnt > 1 AND rn = 1) OR (cnt=1) THEN new_connection1

                    ELSE 0

               END) "Parent_new_connect",

           SUM(CASE WHEN (cnt > 1 AND rn = 1) OR (cnt=1) THEN plan_change1

                    ELSE 0

               END) "Parent_plan_change",

           SUM(new_connection2) "Sharer_new_connect",

           SUM(plan_change2) "Sharer_plan_change"

    FROM

    (SELECT mt1.date_ date_1,

           mt1.plan_code plan_code1,

           mt1.account_no account_no1,

           mt1.new_connection new_connection1,

           mt1.plan_change plan_change1,

           mt2.date_ date_2,

           mt2.plan_code plan_code2,

           mt2.account_no account_no2,

           mt2.new_connection new_connection2,

           mt2.plan_change plan_change2,

           COUNT(mt1.plan_code) OVER(PARTITION BY mt1.date_,mt1.plan_code,mt1.account_no) cnt,

           ROW_NUMBER() OVER(PARTITION BY mt1.date_,mt1.plan_code,mt1.account_no ORDER BY NULL) rn

    FROM mastertable mt1,mastertable mt2

    WHERE mt1.plan_code LIKE 'P%'

    AND mt2.plan_code NOT LIKE 'P%'

    AND mt2.account_no = mt1.account_no

    AND TRUNC(mt1.date_,'MM') = TRUNC(mt2.date_,'MM'))

    GROUP BY TO_CHAR(date_1,'MM-YY'),plan_code1,plan_code2

    ORDER BY 1,2;


    OUTPUT:-

    -------

    Mth_Y Pa Parent_count Sh Sharer_count Parent_new_connect Parent_plan_change Sharer_new_connect Sharer_plan_change

    ----- -- ------------ -- ------------ ------------------ ------------------ ------------------ ------------------

    02-14 P1            2 C1            3                  2                  0                  0                  3

    02-14 P2            1 C1            1                  0                  1                  1                  0

    03-14 P1            2 C1            4                  1                  0                  0                  4

    03-14 P2            1 C1            1                  0                  1                  0                  0

     

  • 6. Re: Parent and sharer plan analsyis- pl/sql
    AnnPricks E Guru
    Currently Being Moderated

    Which i gave given in first reply that is for your first dataset. Now for dataset changing below is the query for new dataset

    INSERT INTO output_table

    SELECT TO_CHAR(date_1,'MM-YY') "Mth_Yr",

           plan_code1 "Parent_code",

           COUNT(DISTINCT date_1||account_no1||plan_code1) "Parent_count",

           plan_code2 "Sharer_code",

           COUNT(plan_code2) "Sharer_count",

           SUM(CASE WHEN (cnt > 1 AND rn = 1) OR (cnt=1) THEN new_connection1

                    ELSE 0

               END) "Parent_new_connect",

           SUM(CASE WHEN (cnt > 1 AND rn = 1) OR (cnt=1) THEN plan_change1

                    ELSE 0

               END) "Parent_plan_change",

           SUM(new_connection2) "Sharer_new_connect",

           SUM(plan_change2) "Sharer_plan_change"

    FROM

    (SELECT mt1.basedate date_1,

           mt1.code plan_code1,

           mt1.account_no account_no1,

           mt1.new_connect new_connection1,

           mt1.plan_change plan_change1,

           mt2.basedate date_2,

           mt2.code plan_code2,

           mt2.account_no account_no2,

           mt2.new_connect new_connection2,

           mt2.plan_change plan_change2,

           COUNT(mt1.code) OVER(PARTITION BY mt1.basedate,mt1.code,mt1.account_no) cnt,

           ROW_NUMBER() OVER(PARTITION BY mt1.basedate,mt1.code,mt1.account_no ORDER BY NULL) rn

    FROM master_table mt1,master_table mt2

    WHERE mt1.code LIKE 'P%'

    AND mt2.code NOT LIKE 'P%'

    AND mt2.account_no = mt1.account_no

    AND TRUNC(mt1.basedate,'MM') = TRUNC(mt2.basedate,'MM'))

    GROUP BY TO_CHAR(date_1,'MM-YY'),plan_code1,plan_code2

    ORDER BY 1,2;


    OUTPUT:-

    ------

    Mth_Y Pa Parent_count Sh Sharer_count Parent_new_connect Parent_plan_change Sharer_new_connect Sharer_plan_change

    ----- -- ------------ -- ------------ ------------------ ------------------ ------------------ ------------------

    02-14 P1            3 C1            6                  3                  0                  3                  3

    02-14 P2            3 C1            5                  1                  2                  2                  3

    02-14 P3            2 C1            4                  1                  1                  2                  2

     

  • 7. Re: Parent and sharer plan analsyis- pl/sql
    2776ebc7-f291-485a-acd2-66d5750db4b6 Newbie
    Currently Being Moderated

    Thanks for the code AnnPricks.

    It works absolutely fine if I check for individual account nos but at a total level, the result is a bit more.

    could it be for a reason that in my database, there are a lot of sharer codes or child codes which do not belong to any parent?


  • 8. Re: Parent and sharer plan analsyis- pl/sql
    AnnPricks E Guru
    Currently Being Moderated

    Can you please post some sample data that is not part of any parent as well

  • 9. Re: Parent and sharer plan analsyis- pl/sql
    AnnPricks E Guru
    Currently Being Moderated

    Can you please explain in which situation you are getting more records.. Because whatever data you are providing, it is working fine and post some more sample data to reproduce the issue.. Because i didn't get anything about account no(Account number should be unique for parent.. Otherwise tell me how to uniquely identify the rows for a particular parent).

  • 10. Re: Parent and sharer plan analsyis- pl/sql
    2776ebc7-f291-485a-acd2-66d5750db4b6 Newbie
    Currently Being Moderated

    Hi AnnPricks.

    The way to uniquiely identify the rows is through 'Phone No'.

    Here is the updated DDL which now include data for blank child codes.

     

    CREATE TABLE Master_Table

    (`BaseDate` datetime, `Account_no` varchar(4), `Phone_no` int, `Code` varchar(2),`New_connect` int,`Plan_change` int )

    ;


    INSERT INTO Master_Table

    (`BaseDate`, `Account_no`, `Phone_no`, `Code`,`New_connect`,`Plan_change`)

    VALUES

        ('2014-02-01 13:00:00', 'A123', 21332, 'P1',1,0),
        ('2014-02-01 13:00:00', 'A123', 21444, 'C1',0,1),
        ('2014-02-01 13:00:00', 'A123', 21445, 'C1',1,0),
        ('2014-02-01 13:00:00', 'A124', 21333, 'P2',1,0),
        ('2014-02-01 13:00:00', 'A124', 21771, 'C1',0,1),
        ('2014-02-01 13:00:00', 'A124', 21772, 'C1',0,1),
        ('2014-02-01 13:00:00', 'A124', 21773, 'C1',1,0),
        ('2014-02-01 13:00:00', 'A126', 213321,'P1',1,0),
        ('2014-02-01 13:00:00', 'A126', 214443,'C1',0,1),
        ('2014-02-01 13:00:00', 'A126', 214452,'C1',1,0),
        ('2014-02-01 13:00:00', 'A129', 213331,'P3',1,0),
        ('2014-02-01 13:00:00', 'A129', 21771, 'C1',0,1),
        ('2014-02-01 13:00:00', 'A129', 21772, 'C1',0,1),
        ('2014-02-01 13:00:00', 'A129', 21773, 'C1',1,0),
        ('2014-02-02 13:00:00', 'B123', 2133222,'P1',1,0),
        ('2014-02-02 13:00:00', 'B123', 2133222,'C1',1,0),
        ('2014-02-02 13:00:00', 'B123', 2133229,'C1',0,1),
        ('2014-02-02 13:00:00', 'H123', 21654,'P2',0,1),
        ('2014-02-02 13:00:00', 'H123', 21875,'C1',0,1),
        ('2014-02-03 13:00:00', 'V123', 2133222,'P3',0,1),
        ('2014-02-03 13:00:00', 'V123', 2133212,'C1',1,0),
        ('2014-02-07 13:00:00', 'Z123', 2131234,'P2',0,1),
        ('2014-02-07 13:00:00', 'Z123', 213123433,'C1',1,0),
        ('2014-02-01 13:00:00', 'q123', 9321, 'C1',1,0),
        ('2014-02-01 13:00:00', 'x123', 923321, 'C1',1,0),
        ('2014-02-02 13:00:00', 'qq1', 119321, 'C1',0,1),
        ('2014-02-02 13:00:00', 'G23', 097321, 'C1',1,0),
        ('2014-02-03 13:00:00', 'U23', 456321, 'C1',0,1),
        ('2014-02-03 13:00:00', 'Y65', 9321, 'C1',1,0);

     

     


    Thanks

  • 11. Re: Parent and sharer plan analsyis- pl/sql
    AnnPricks E Guru
    Currently Being Moderated

    Hi...

    I didn't get your requirement properly.. Can you please tell me which child is not part of any child.. Can you please explain with your data?

  • 12. Re: Parent and sharer plan analsyis- pl/sql
    2776ebc7-f291-485a-acd2-66d5750db4b6 Newbie
    Currently Being Moderated

    Hi AnnPricks

    As I mentioned before,

     

    parents and children share the same account no but they all have different phone nos.

    Now in my latest ddl,

    ('2014-02-01 13:00:00', 'q123', 9321, 'C1',1,0),

        ('2014-02-01 13:00:00', 'x123', 923321, 'C1',1,0),

        ('2014-02-02 13:00:00', 'qq1', 119321, 'C1',0,1),

        ('2014-02-02 13:00:00', 'G23', 097321, 'C1',1,0),

        ('2014-02-03 13:00:00', 'U23', 456321, 'C1',0,1),

        ('2014-02-03 13:00:00', 'Y65', 9321, 'C1',1,0);

     

    All these child codes do not belong to any parent as none of the parent codes in the master data have the same account nos as these children above.

    For ex ('2014-02-01 13:00:00', 'q123', 9321, 'C1',1,0) has account no 'q123' and this account no does not belong to any 'P%' or parent code.

    There are no records of any parent code having this account no and therefore I do not want to count these children or sharers who do not belong to any parent code.

     

    Hope this helps.

  • 13. Re: Parent and sharer plan analsyis- pl/sql
    AnnPricks E Guru
    Currently Being Moderated

    Then my query should work.. Because i am matching only account_no not anything more.. While running my above query i am getting correct result.. See below result for your dataset.. It is not counting the child which is not belongs to any parent.

    Mth_Y Pa Parent_count Sh Sharer_count Parent_new_connect Parent_plan_change Sharer_new_connect Sharer_plan_change

    ----- -- ------------ -- ------------ ------------------ ------------------ ------------------ ------------------

    02-14 P1            3 C1            6                  3                  0                  3          3

    02-14 P2            3 C1            5                  1                  2                  2          3

    02-14 P3            2 C1            4                  1                  1                  2          2

  • 14. Re: Parent and sharer plan analsyis- pl/sql
    2776ebc7-f291-485a-acd2-66d5750db4b6 Newbie
    Currently Being Moderated

    Hi Annpricks

    I think I know what the issue is...

    In my source table, I found some instances where the same phone no got connected the one day and within a day or two did a plan change .

    I will explain it with the data

    CREATE TABLE Master_Table

    (`BaseDate` datetime, `Account_no` varchar(4), `Phone_no` int, `Code` varchar(2),`New_connect` int,`Plan_change` int )

    ;


    INSERT INTO Master_Table

    (`BaseDate`, `Account_no`, `Phone_no`, `Code`,`New_connect`,`Plan_change`)

    VALUES

        ('2014-02-01 13:00:00', 'A123', 21332, 'P1',1,0),
        ('2014-02-01 13:00:00', 'A123', 21444, 'C1',0,1),
        ('2014-02-01 13:00:00', 'A123', 21445, 'C1',1,0),
        ('2014-02-01 13:00:00', 'A124', 21333, 'P2',1,0),
        ('2014-02-01 13:00:00', 'A124', 21771, 'C1',0,1),
        ('2014-02-01 13:00:00', 'A124', 21772, 'C1',0,1),
        ('2014-02-01 13:00:00', 'A124', 21773, 'C1',1,0),
        ('2014-02-01 13:00:00', 'A126', 213321,'P1',1,0),
        ('2014-02-01 13:00:00', 'A126', 214443,'C1',0,1),
        ('2014-02-01 13:00:00', 'A126', 214452,'C1',1,0),
        ('2014-02-01 13:00:00', 'A129', 213331,'P3',1,0),
        ('2014-02-01 13:00:00', 'A129', 21771, 'C1',0,1),
        ('2014-02-01 13:00:00', 'A129', 21772, 'C1',0,1),
        ('2014-02-01 13:00:00', 'A129', 21773, 'C1',1,0),
        ('2014-02-02 13:00:00', 'B123', 2133222,'P1',1,0),
        ('2014-02-02 13:00:00', 'B123', 2133222,'C1',1,0),
        ('2014-02-02 13:00:00', 'B123', 2133229,'C1',0,1),
        ('2014-02-02 13:00:00', 'H123', 21654,'P2',0,1),
        ('2014-02-02 13:00:00', 'H123', 21875,'C1',0,1),
        ('2014-02-03 13:00:00', 'V123', 2133222,'P3',0,1),
        ('2014-02-03 13:00:00', 'V123', 2133212,'C1',1,0),
        ('2014-02-07 13:00:00', 'Z123', 2131234,'P2',0,1),
        ('2014-02-07 13:00:00', 'Z123', 213123433,'C1',1,0),
        ('2014-02-01 13:00:00', 'q123', 9321, 'C1',1,0),
        ('2014-02-01 13:00:00', 'x123', 923321, 'C1',1,0),
        ('2014-02-02 13:00:00', 'qq1', 119321, 'C1',0,1),
        ('2014-02-02 13:00:00', 'G23', 097321, 'C1',1,0),
        ('2014-02-03 13:00:00', 'U23', 456321, 'C1',0,1),
        ('2014-02-03 13:00:00', 'Y65', 9321, 'C1',1,0),
        ('2014-02-03 13:00:00', 'B01', 2135454, 'P1',0,1),
        ('2014-02-03 13:00:00', 'B01', 21354523, 'C1',1,0),
        ('2014-02-06 13:00:00', 'B01', 21354523, 'C1',0,1),
        ('2014-02-03 13:00:00', 'C01', 218787, 'P1',0,1),
        ('2014-02-03 13:00:00', 'C01', 21878766, 'C1',1,0),
        ('2014-02-09 13:00:00', 'C01', 21878766, 'C1',0,1);
       
        See here in my last two account nos - 'B01' and 'C01',the same phone no is a new connection on one day and changes plans on the next day or so.

     

    Hope it  makes sense.

     

    Regards


      

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points