Post reply

Count for each account no -parent and childsql

  • November 21, 2013 at 5:56 pm

    #1668274

    Hi Jeff

    Here is what I want:

    create table #Something

    (

    Line_no int,

    code varchar(5),

    AccountNo char(5)

    )

    insert #Something

    select 12311, 'P1c', 'Ac115' union all

    select 12311, 'L1', 'Ac115' union all

    select 123, 'C1', 'Ac115' union all

    select 1222, 'C1', 'Ac115' union all

    select 1243, 'C1', 'Ac115' union all

    select 433, 'P1a', 'Ac111' union all

    select 433, 'L1', 'Ac111' union all

    select 4331, 'C1', 'Ac111' union all

    select 543, 'C1', 'Ac222' union all

    select 544, 'C1', 'Ac222' union all

    select 4322, 'P1b', 'Ac222' union all

    select 4322, 'L1', 'Ac222' union all

    select 8766 'P1d' , 'Ab111' union all

    select 8767 'C1', 'Ab111' union all

    select 8789 'C1', ' Ab111' union all

    select 8766 'L1', ' Ab111'

    select * from #Something

    drop table #Something

    Desired output is:

    [Parent code] [Parent line Count] [Child line Count]

    P1c 1 3

    P1a 1 1

    P1b 1 2

    P1d 1 2

  • November 21, 2013 at 6:31 pm

    #1668279

    Your test data is flawed because it is missing some commas in the INSERT/SELECT and you get truncation errors putting it into the data types you supplied in the CREATE TABLE (see comment below). Nonetheless, this runs:

    create table #Something

    (

    Line_no int,

    code varchar(5),

    AccountNo char(5)

    )

    insert #Something

    select 12311, 'P1c', 'Ac115' union all

    select 12311, 'L1', 'Ac115' union all

    select 123, 'C1', 'Ac115' union all

    select 1222, 'C1', 'Ac115' union all

    select 1243, 'C1', 'Ac115' union all

    select 433, 'P1a', 'Ac111' union all

    select 433, 'L1', 'Ac111' union all

    select 4331, 'C1', 'Ac111' union all

    select 543, 'C1', 'Ac222' union all

    select 544, 'C1', 'Ac222' union all

    select 4322, 'P1b', 'Ac222' union all

    select 4322, 'L1', 'Ac222' union all

    select 8766, 'P1d' , 'Ab111' union all

    select 8767, 'C1', 'Ab111' union all

    select 8789, 'C1', 'Ab111' union all -- last 2 rows had leading space in last column

    select 8766, 'L1', 'Ab111'

    And this delivers your expected results:

    SELECT [Parent code]=a.code

    ,[Parent Line Count]=COUNT(a.Line_no) OVER (PARTITION BY a.code)

    ,[Child Line Count]=COUNT(*)

    FROM #Something a

    JOIN #Something b ON a.Line_No = b.Line_No AND

    LEFT(a.code, 1) = 'P' AND LEFT(b.code, 1) = 'L'

    JOIN #Something c ON b.AccountNo = c.AccountNo AND

    LEFT(c.code, 1) NOT IN ('P', 'L')

    GROUP BY a.code, a.Line_no;

    However, none of your test data is constructed for a case where the [Parent Line Count] may be > 1. So I must leave that up to you to test.

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • November 22, 2013 at 3:05 am

    #1668332

    Hi

    Thanks for the code.

    You are right.

    I have multiple lines of same parent code in my database.

    How do I deal with that? ?

    Pls help !!!

  • November 22, 2013 at 3:42 am

    #1668342

    nutty (11/22/2013)

    Hi

    Thanks for the code.

    You are right.

    I have multiple lines of same parent code in my database.

    How do I deal with that? ?

    Pls help !!!

    Expand your test data and give us the expected results.

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • November 22, 2013 at 12:04 pm

    #1668503

    Here it is

    create table #Something

    (

    Line_no int,

    code varchar(5),

    AccountNo char(5)

    )

    insert #Something

    select 12311, 'P1c', 'Ac115' union all

    select 12311, 'L1', 'Ac115' union all

    select 123, 'C1', 'Ac115' union all

    select 1222, 'C1', 'Ac115' union all

    select 1243, 'C1', 'Ac115' union all

    select 433, 'P1a', 'Ac111' union all

    select 433, 'L1', 'Ac111' union all

    select 4331, 'C1', 'Ac111' union all

    select 543, 'C1', 'Ac222' union all

    select 544, 'C1', 'Ac222' union all

    select 4322, 'P1b', 'Ac222' union all

    select 4322, 'L1', 'Ac222' union all

    select 8766 'P1d' , 'Ab111' union all

    select 8766 'L1' , 'Ab111' union all

    select 8767 'C1', 'Ab111' union all

    select 8789 'P1d', 'Ab119' union all

    select 8766 'L1', 'Ab119' union all

    select 876654 'C1', 'Ab119' union all

    select 876655 'C1', 'Ab119' union all

    select 876698 'P1a', 'Ab117' union all

    select 876698 'L1', 'Ab117' union all

    select 987 'C1', 'Ab117' union all

    select 555444 'P1d' 'Xcv' union all

    select 555444 'L1' 'Xcv' union all

    select 6754 'C1' 'Xcv' union all

    select * from #Something

    drop table #Something

    Desired output is:

    [Parent code] [Parent line Count] [Child line Count]

    P1c 1 3

    P1a 2 2

    P1b 1 2

    P1d 3 4

  • November 24, 2013 at 5:31 pm

    #1668698

    nutty (11/22/2013)

    [Parent code] [Parent line Count] [Child line Count]

    P1c 1 3

    P1a 2 2

    P1b 1 2

    P1d 3 4

    Are you sure about those expected results?

    A couple of minor modifications to what I gave you before gets you close.

    SELECT [Parent code]=a.code

    ,[Parent Line Count]=COUNT(DISTINCT a.Line_no) -- OVER (PARTITION BY a.code)

    ,[Child Line Count]=COUNT(*)

    FROM #Something a

    JOIN #Something b ON a.Line_No = b.Line_No AND

    LEFT(a.code, 1) = 'P' AND LEFT(b.code, 1) = 'L'

    JOIN #Something c ON b.AccountNo = c.AccountNo AND

    LEFT(c.code, 1) NOT IN ('P', 'L')

    GROUP BY a.code; --, a.Line_no;

    You'll probably want to play around with this to see where it takes you.

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • February 13, 2014 at 2:42 am

    #1688640

    Hi Guys

    I think I mixed two requiements in 1 query.

    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.

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply