Help with Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with Query

Author

Topic

Fishwagon
Starting Member

10 Posts

Posted - 2007-11-08 : 10:47:21

Hello everyone,

I'm trying to do a query but I can't seem to nail it down.

I am joining two tables in two different datbases that are joined by a phone number.

The first table shows data in incoming calls. It's the datbase from our phone system. So that basically says "A phone call came in from number ###-###-#### at this date, time, etc, etc, etc,

The table in the other database has our contact info. I'm trying to write a query so we can use a report that would show WHO called. So I'd like to say "A call came in from number ###-###-#### and it was John Doe from ABC Company.

However, some numbers have multiple entries in the second table (as that number may be the main line ABC Company). So the way my join is currently contstructed, you guess it, it is putting in multiple rows for those phone calls that have multiple hits in the contacts table, so it says "Three calls came in at the same exact time from teh same company, from John, Peter, and Jane, where it was only John that called.

How can I join two tables, but if the joined table has multiple entries for the parameter supplied, limit the return? Or am I not looking at this the right way?

Here is current query

SELECT
dateAdd(hour,datediff(hour, getutcdate(), getdate()),dateadd(second, (A.StartTime + GMTOffset), '1/1/1970 12:00 AM')) As StartTime,
dateAdd(hour,datediff(hour, getutcdate(), getdate()),dateadd(second, (A.EndTime + GMTOffset), '1/1/1970 12:00 AM')) As EndTime,
Case A.CallerType
When 1 Then 'Internal Call'
When 10 Then 'External Call'
Else 'Unknown'
End As [Call Type],
Case A.Direction
When 1 Then 'Incoming'
When 2 Then 'Outgoing'
End As [Call Direction],
A.CallerNum As [Caller Number],
A.CallerName As [Caller Name Reported by AltiGen],
B.FirstName As [Corp Contact First Name],
B.LastName As [Corp Contact Last Name],
B.Company As [Corp Contact Company Name],
A.TargetNum As [Number Called],
A.TargetName As [Internal Person Called],
A.RingDuration As [Seconds Phone Rang],
A.VMDuration As [Voice Mail Duration in Seconds],
A.HoldDuration As [Hold Time in Seconds],
A.TalkDuration / 60 As [Talk Time in Minutes]
From AltiGenArchive.dbo.CDRMAIN A LEFT JOIN
CorpContacts.dbo.Contacts B ON A.CallerNum = B.BusinessPhone)
Order By A.StartTime Asc

This could also be a "ID 10 T" error, I admit....

Any help would be appreciated.

Rich

Thanks and have a great day...

Rich

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-08 : 18:12:07

How do you want to limit the return? Last phone call, first phone call... first call from NAME first call from Company etc...

At what level do you want the row level return to be limited? That makes the difference in how to answer.

Fishwagon
Starting Member

10 Posts

Posted - 2007-11-08 : 19:00:32

What I was going to try to implement, was that if a call was logged from a number that has multiple entries in the contacts table, I was going to replace the value with something along the lines of "Non-Unique Number" or something just so the person reading the report would understand it came from a "commom" phone number.

So if I get a phone call from 123-456-7890, and in the contacts table there are three names (Based on B.FirstName, B.LastName) all of which would have the same Company Name, I would disregard all names, and just replace the value with something like I described about.

I'm just drawing a blank on this......

Thank you for your help...it is appreciated....

Thanks and have a great day...

Rich

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-08 : 19:15:15

That doesn't really answer the question, it is now a different question.

What do you want to do with a phone number that has multiple entries? Replace the name with "Multiple"?



SELECT DISTINCT
dateAdd(hour,datediff(hour, getutcdate(), getdate()),dateadd(second, (A.StartTime + GMTOffset), '1/1/1970 12:00 AM')) As StartTime,
dateAdd(hour,datediff(hour, getutcdate(), getdate()),dateadd(second, (A.EndTime + GMTOffset), '1/1/1970 12:00 AM')) As EndTime,
Case A.CallerType
		When 1 Then 'Internal Call'
		When 10 Then 'External Call'
		Else 'Unknown'
		End As [Call Type],
Case A.Direction
		When 1 Then 'Incoming'
		When 2 Then 'Outgoing'
		End As [Call Direction],
A.CallerNum As [Caller Number],
A.CallerName As [Caller Name Reported by AltiGen],
Case When C.CountCalls > 1 then 'Multiple' 
	else B.FirstName As [Corp Contact First Name],
Case When C.CountCalls > 1 then 'Multiple' 
	else B.LastName As [Corp Contact Last Name],
B.Company As [Corp Contact Company Name],
A.TargetNum As [Number Called],
A.TargetName As [Internal Person Called],
A.RingDuration As [Seconds Phone Rang],
A.VMDuration As [Voice Mail Duration in Seconds],
A.HoldDuration As [Hold Time in Seconds],
A.TalkDuration / 60 As [Talk Time in Minutes]
From AltiGenArchive.dbo.CDRMAIN A 
			LEFT JOIN CorpContacts.dbo.Contacts B 
			ON A.CallerNum = B.BusinessPhone
			Left Join (Select Count(*)as CountCalls,BusinessPhone
				   FROM CorpContacts.dbo.Contacts
				   GROUP BY BusinessPhone) C
			On a.CallerNum = B.BusinessPhone
GROUP BY 

Order By A.StartTime Asc

By creating a derived table with the count and left joining you can CASE/WHEN for the count> 1
DISTINCT will make the rows unique. Maybe, untested.

Reporter
Starting Member

48 Posts

Posted - 2007-11-09 : 03:17:59

Can you write simple model of your problem?

For example something like this

declare @A table (NID varchar(50), Calltime datetime)
insert @A
select '444-444-4444','11.01.2007' union all
select '333-333-3333','11.02.2007' union all
select '222-222-2222','11.03.2007' union all
select '333-333-3333','11.04.2007'

declare @B table (NID varchar(50), Person varchar(50), Company varchar(50))
insert @B
select '333-333-3333','name1','company1' union all
select '333-333-3333','name2','company1' union all
select '111-111-1111','name3','company2' union all
select '222-222-2222','name2','company3'

report

'11.01.2007','444-444-4444','unknown','unknown'
'11.01.2007','333-333-3333','non-unique','company1'
'11.01.2007','222-222-2222','name2','company3'
'11.01.2007','333-333-3333','non-unique','company1'

Is this right model or I miss something?

select a.Calltime,a.NID,b.Person,b.Company
from @A a left join
(select NID,Person=case when count(*)=1 then max(Person) else 'non-unique' end,Company from @B group by NID,Company) b
on a.NID=b.NID

Fishwagon
Starting Member

10 Posts

Posted - 2007-11-09 : 05:38:17

Thank you both for your input, it is very much appreciated. Dataguru's query returns what I needed.

Thanks again and have a great weekend....

Rich

Thanks and have a great day...

Rich

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources