SQL Server Performance Forum – Threads Archive

Question on Stored Procedure

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.

---

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 —————————————-

---

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

---

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 —————————————-

---

Read about Cross-tab Reports in sql server help file Madhivanan Failing to plan is Planning to fail

---

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

---

]]>