June 4, 2010 at 2:34 am
Dear all,
I'm doing a complex task to get the output like
ExtensionNo---- UserId---- I/C-Int----O/T-Int------1st---- 2nd-------3rd
301---------------- 2 -------- 1 ---------19 -------319-4 ----303-3 ----317-15
320--------------- 17-------- 0 -------- 21 -------304-21----303-3 ----319-10
where
being most dialed extensions - at 1st,2nd,3rd column
eg : 301 have 2 Incoming Internal, 1 Outgoing Internal calls among that
most dialed numbers(Called number) are 319,303,317 from 301 extension.
the above example shows the Extension with UserId I.E 319 is extension - 4 is userid for 319.
From the call record we find out "Call_Type_Int=4" means Incoming Internal
"Call_Type_Int=3" means Outgoing Internal
I use
sum(Case When Call_Type_Int=4 then 1 else 0 End) as 'I/C INT',
sum(Case When Call_Type_Int=3 then 1 else 0 End) as 'O/G INT',
for column I/C-Int O/T-Int in above example
How can i get the value for the 1st, 2nd and 3rd column (most dialed number with userId)
Please help
I Attached the script file for table with data.
June 4, 2010 at 3:05 am
Hi,
I might be totally missing the point here, but wouldn't the following provide the information you require?
select
[UserID],
[Extension],
[Called_Number] [Called Number],
case [Call_Type_Int]
when 3 then 'O/G INT'
when 4 then 'I/C INT'
end [Call Type],
count(1) [Number Of Calls Made]
from dbo.[TestTable]
group by
[UserID],
[Extension],
[Called_Number],
[Call_Type_Int]
having count(1) > 0
order by [UserID]
go
www.sqlAssociates.co.uk
June 4, 2010 at 3:20 am
Thanks for for your reply.
I want the first 5 most Frequently Dialed numbers with the userid of called extension in the column.
ie 301 had mostly called to 302 -6 time,303-5 time ,304- 2 times,305 4 times means it should be as
301----(302-2)---(303-3)----(305-5)----(304-4)
where (302 is extension, 2 is the user id for 302)
June 4, 2010 at 11:49 am
Not sure exactly how you want to do groupings and the data you provided didn't exactly match up with the results but hopefully this will give you an idea of how you can get what you want.
Also, I used a temp table instead of a regular table. The data is all yours though.
CREATE TABLE #TestTable
(
[call_DateTime] [datetime] NULL,
[Extension] [varchar](255) NULL,
[called_Number] [varchar](255) NULL,
[Call_Type_Int] [bigint] NULL,
[Call_Description] [varchar](255) NULL,
[Userid] [bigint] NULL
)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D3500CB7780 AS DateTime), N'301', N'319', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D3500CB7780 AS DateTime), N'319', N'301', 4, N'Incoming Internal', 10)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2C01808580 AS DateTime), N'301', N'319', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2C01808580 AS DateTime), N'319', N'301', 4, N'Incoming Internal', 10)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2C018344A0 AS DateTime), N'301', N'303', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2C018344A0 AS DateTime), N'303', N'301', 4, N'Incoming Internal', 3)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2C018344A0 AS DateTime), N'301', N'319', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2C018344A0 AS DateTime), N'319', N'301', 4, N'Incoming Internal', 10)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2C0183D140 AS DateTime), N'301', N'319', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2C0183D140 AS DateTime), N'319', N'301', 4, N'Incoming Internal', 10)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2C0184A430 AS DateTime), N'301', N'319', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2C0184A430 AS DateTime), N'319', N'301', 4, N'Incoming Internal', 10)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2C0188C2E0 AS DateTime), N'301', N'319', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2C0188C2E0 AS DateTime), N'319', N'301', 4, N'Incoming Internal', 10)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2C018AF560 AS DateTime), N'301', N'303', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2C018AF560 AS DateTime), N'303', N'301', 4, N'Incoming Internal', 3)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00BD83A0 AS DateTime), N'301', N'303', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00BD83A0 AS DateTime), N'303', N'301', 4, N'Incoming Internal', 3)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00C042C0 AS DateTime), N'301', N'303', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00C042C0 AS DateTime), N'303', N'301', 4, N'Incoming Internal', 3)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00C301E0 AS DateTime), N'320', N'321', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00C301E0 AS DateTime), N'321', N'320', 4, N'Incoming Internal', 14)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00CDFE60 AS DateTime), N'320', N'321', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00CDFE60 AS DateTime), N'321', N'320', 4, N'Incoming Internal', 14)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'320', N'321', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'321', N'320', 4, N'Incoming Internal', 14)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00EEF3E0 AS DateTime), N'301', N'317', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00EEF3E0 AS DateTime), N'317', N'301', 4, N'Incoming Internal', 15)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00FF6EA0 AS DateTime), N'301', N'317', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00FF6EA0 AS DateTime), N'317', N'301', 4, N'Incoming Internal', 15)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00FF6EA0 AS DateTime), N'301', N'317', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00FF6EA0 AS DateTime), N'317', N'301', 4, N'Incoming Internal', 15)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D010FE960 AS DateTime), N'301', N'317', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D010FE960 AS DateTime), N'317', N'301', 4, N'Incoming Internal', 15)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00FF6EA0 AS DateTime), N'301', N'315', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00FF6EA0 AS DateTime), N'315', N'301', 4, N'Incoming Internal', 15)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00FF6EA0 AS DateTime), N'301', N'315', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00FF6EA0 AS DateTime), N'315', N'301', 4, N'Incoming Internal', 15)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D010FE960 AS DateTime), N'301', N'315', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D010FE960 AS DateTime), N'315', N'301', 4, N'Incoming Internal', 15)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00EEF3E0 AS DateTime), N'301', N'315', 4, N'Incoming Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00FF6EA0 AS DateTime), N'315', N'301', 3, N'Outgoing Internal', 24)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00FF6EA0 AS DateTime), N'301', N'316', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00FF6EA0 AS DateTime), N'316', N'301', 4, N'Incoming Internal', 9)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00FF6EA0 AS DateTime), N'301', N'316', 3, N'Outgoing Internal', 2)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00FF6EA0 AS DateTime), N'316', N'301', 4, N'Incoming Internal', 9)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00C301E0 AS DateTime), N'320', N'319', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00C301E0 AS DateTime), N'319', N'320', 4, N'Incoming Internal', 10)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00CDFE60 AS DateTime), N'320', N'319', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00CDFE60 AS DateTime), N'319', N'320', 4, N'Incoming Internal', 10)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'320', N'319', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'319', N'320', 4, N'Incoming Internal', 10)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00C301E0 AS DateTime), N'320', N'303', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00C301E0 AS DateTime), N'303', N'320', 4, N'Incoming Internal', 3)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00CDFE60 AS DateTime), N'320', N'303', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00CDFE60 AS DateTime), N'303', N'320', 4, N'Incoming Internal', 3)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'320', N'303', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'303', N'320', 4, N'Incoming Internal', 3)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'320', N'303', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'303', N'320', 4, N'Incoming Internal', 3)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00CDFE60 AS DateTime), N'320', N'304', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00CDFE60 AS DateTime), N'304', N'320', 4, N'Incoming Internal', 21)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'320', N'304', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'304', N'320', 4, N'Incoming Internal', 21)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'320', N'304', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'304', N'320', 4, N'Incoming Internal', 21)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'320', N'304', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'304', N'320', 4, N'Incoming Internal', 21)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'320', N'304', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'304', N'320', 4, N'Incoming Internal', 21)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00CDFE60 AS DateTime), N'320', N'317', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00CDFE60 AS DateTime), N'317', N'320', 4, N'Incoming Internal', 15)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'320', N'317', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'317', N'320', 4, N'Incoming Internal', 15)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'320', N'317', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'317', N'320', 4, N'Incoming Internal', 15)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'320', N'317', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'317', N'320', 4, N'Incoming Internal', 15)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'320', N'317', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'317', N'320', 4, N'Incoming Internal', 15)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'320', N'317', 3, N'Outgoing Internal', 17)
INSERT #TestTable ([call_DateTime], [Extension], [called_Number], [Call_Type_Int], [Call_Description], [Userid]) VALUES (CAST(0x00009D2D00DE7920 AS DateTime), N'317', N'320', 4, N'Incoming Internal', 15);
There is probably a cleaner way to do this, but this works:
with cteTemp(Extension, UserID, CalledExt, CalledNo)
as
(
select Extension,
Userid,
called_Number,
COUNT(call_DateTime)
from #TestTable
group by Extension, Userid, called_Number
),
cteTempFinal(Extension, UserID, CalledExt, CalledNo, OrderNum)
as
(
select Extension,
UserID,
CalledExt,
CalledNo,
ROW_NUMBER() OVER (PARTITION BY Extension, UserID ORDER BY CalledNo DESC)
from cteTemp
),
cteCounts(Extension, UserID, Incoming, Outgoing)
as
(
select sq.Extension,
sq.Userid,
SUM(sq.Incoming),
SUM(sq.Outgoing)
from
(
select Extension,
Userid,
Incoming = case Call_Type_Int when 4 then COUNT(call_DateTime) end,
Outgoing = case Call_Type_Int when 3 then COUNT(call_DateTime) end
from #TestTable
group by Extension, Userid, Call_Type_Int
) sq
group by sq.Extension, sq.Userid
)
select c1.Extension,
c1.UserID,
isnull(cc.Incoming,0) as 'I/C-Int',
isnull(cc.Outgoing,0) as 'O/T-Int',
cast(c1.CalledExt as varchar) + '-' + CAST(c1.CalledNo as varchar) as '1st',
cast(c2.CalledExt as varchar) + '-' + CAST(c2.CalledNo as varchar) as '2nd',
cast(c3.CalledExt as varchar) + '-' + CAST(c3.CalledNo as varchar) as '3rd'
from cteTempFinal c1
join cteCounts cc
on cc.Extension = c1.Extension
and cc.UserID = c1.UserID
left join cteTempFinal c2
on c2.Extension = c1.Extension
and c2.UserID = c1.UserID
and c2.OrderNum = 2
left join cteTempFinal c3
on c3.Extension = c1.Extension
and c3.UserID = c1.UserID
and c3.OrderNum = 3
where c1.OrderNum = 1
order by c1.Extension, c1.UserID
drop table #TestTable
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply