SQL Server Performance

Question on Stored Procedure

Discussion in 'SQL Server 2005 General Developer Questions' started by nickm324, Jul 14, 2006.

  1. nickm324 New Member

    Question:

    I have a table that has for example the following columns.... (had to put dashes for spaces because this editor keeps stripping the spaces from the layout)

    PhoneLine------Completed
    1000--------------1
    1004--------------0
    1001--------------1
    1002--------------0
    1003--------------1
    1004--------------0
    1005--------------0
    1002--------------1
    1004--------------0

    How can I get a count of each row that is completed(1) and a count of each row that is incomplete (0) and be able to create a result that is in this form.

    PhoneLine----Completed Call count------Incomplete Call Count
    1000--------------1
    1002--------------1---------------------------1
    1003--------------1
    1004------------------------------------------3
    1005----------------------------------------- 1

    If you notice for example line 1000 has only 1 completed and no incomplete and line 1004 has 3 Incompelte but no completed.

    I need to be able to get a list of all lines that exist and then a count for each line for completed and incomplete.

    Problem I have having is that some Lines have either no Completed or Incomplete value.

    I need to generate a result set of all existing lines and if there is no value for a specific line of either or both of the Completed or Incomplete values just substitue a zero in place of the missing value to return in the result set.

    Basicly so that every line has a value for each completed and incompelte count even if it is a zero if nothing exists.


    This is all being done in a stored procedure. I have a version that works fine as long as there is at least 1 value per line for completed and incomplete, but when there is no result for one or the other I have problems.

    I need to be able to force a value for each line that has a missing value for completed or incomplete with a 0 (zero).

    For example there maybe 500 rows for line 1003 that are set as completed and none for incomplete or vise versa and that is where I have the problem.

    this result set is being returned to an asp page.

    Any help would be greatly appreciated.
  2. dineshasanka Moderator

    will this work

    Select P.*,Q.Uncompleted from (select phoneline,count(*) Completed from call where completed = 1 group by phoneline) P
    full outer join
    (select phoneline,count(*) Uncompleted
    from call where completed = 0 group by phoneline) Q on
    P.phoneline = Q.phoneline

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

  3. ranjitjain New Member

    check this:

    declare @t1 table(col1 int,col2 int)

    insert into @t1
    select 1000,1 union all
    select 1004,0 union all
    select 1001,1 union all
    select 1002,0 union all
    select 1003,1 union all
    select 1004,0 union all
    select 1005,0 union all
    select 1002,1 union all
    select 1004,0

    select col1, sum(callscompleted) as callscompleted, sum(callsincomplete) as callsincomplete
    from(
    select col1,case col2 when 1 then 1 else 0 end as callscompleted,
    case col2 when 1 then 0 else 1 end as callsincomplete from @t1)temp
    group by col1

    -------------------------------------
    col1 callscompleted callsincomplete
    ----------- -------------- ---------------
    1000 1 0
    1001 1 0
    1002 1 1
    1003 1 0
    1004 0 3
    1005 0 1
  4. dineshasanka Moderator

    my query needs a fix
    here it is

    Select case when P.PL1 is null then Q.PL2 else P.PL1 end,
    P.completed,Q.Uncompleted from (select phoneline PL1,count(*) Completed
    from call where completed = 1 group by phoneline) P
    full outer join
    (select phoneline PL2,count(*) Uncompleted
    from call where completed = 0 group by phoneline) Q on
    P.Pl1 = Q.Pl2



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

  5. Madhivanan Moderator

    Read about Cross-tab Reports in sql server help file

    Madhivanan

    Failing to plan is Planning to fail
  6. nickm324 New Member

    Well problem with that is I am working with servral sites and each have a different list of phone lin ID's so the SP will have to read in from a table all the lines that are listed then look for all completed calls and then all uncompleted calls and then make pairs out of all the lines that have compelte and incomplete values



    quote:Originally posted by ranjitjain

    check this:

    declare @t1 table(col1 int,col2 int)

    insert into @t1
    select 1000,1 union all
    select 1004,0 union all
    select 1001,1 union all
    select 1002,0 union all
    select 1003,1 union all
    select 1004,0 union all
    select 1005,0 union all
    select 1002,1 union all
    select 1004,0

    select col1, sum(callscompleted) as callscompleted, sum(callsincomplete) as callsincomplete
    from(
    select col1,case col2 when 1 then 1 else 0 end as callscompleted,
    case col2 when 1 then 0 else 1 end as callsincomplete from @t1)temp
    group by col1

    -------------------------------------
    col1 callscompleted callsincomplete
    ----------- -------------- ---------------
    1000 1 0
    1001 1 0
    1002 1 1
    1003 1 0
    1004 0 3
    1005 0 1

Share This Page