Relation between tables

Guys,

I have a "PABX' that records all callls did..and I send it to a table and my call operator send me my account details eletronically so I can import it to another table

So in both tables I have

Date and time

Telephone number

Location(of the call city - state)

Call type(DDD, DDI e LOCAL, VC* used for cell phone)

Duration(in minutes)

For both cases i need to

-recover all rows that the operator table has and mine don't

-recover all rows that the my table has and operator don't

-recover all rows that are in both tables

But there is some problens

- the duration of the calls are different from one table to another

- the datetime is different(the day is the same but the time don't)

Any suggestions examples I would appreciate...I have spent a long time on it and did'nt got a good result..

Hi,

Let's me analyze the situation :

- The operator time is different from your local time. So the time cannot be in the matching condition. However the Date can be included in the criteria. Of course for calls between 00:00 +- n seconds u can have problems because u can have 1 day difference between the two dates. But i think we can assume this.

- The Telephone number, the call city and the call type can be included in the matching criteria.

- The duration can be also included in the matching criteria because if there is some difference you want it to be logged in the differences list.

Correct me if i am wrong.

I Created two tables : OperatorLog and PABXLog with theses columns (CallDate, PhoneNumber, Location, CallType, Duration).

Here the sql need for each request :

- all rows that the operator table has and yours don't :

Code Snippet

SELECT dbo.OperatorLog.PhoneNumber, dbo.OperatorLog.Location, dbo.OperatorLog.CallType, dbo.OperatorLog.Duration, dbo.OperatorLog.CallDateTime
FROM dbo.OperatorLog LEFT OUTER JOIN
dbo.PABXLog ON DAY(dbo.OperatorLog.CallDateTime) = DAY(dbo.PABXLog.CallDateTime) AND MONTH(dbo.OperatorLog.CallDateTime)
= MONTH(dbo.PABXLog.CallDateTime) AND YEAR(dbo.OperatorLog.CallDateTime) = YEAR(dbo.PABXLog.CallDateTime) AND
dbo.OperatorLog.Duration = dbo.PABXLog.Duration AND dbo.OperatorLog.CallType = dbo.PABXLog.CallType AND
dbo.OperatorLog.Location = dbo.PABXLog.Location AND dbo.OperatorLog.PhoneNumber = dbo.PABXLog.PhoneNumber
WHERE (dbo.PABXLog.PhoneNumber IS NULL)

- all rows that the your table has and operator don't (reversed query) :

Code Snippet

SELECT dbo.PABXLog.PhoneNumber, dbo.PABXLog.Location, dboPABXLog.CallType, dbo.PABXLog.Duration, dbo.PABXLog.CallDateTime
FROM dbo.PABXLog LEFT OUTER JOIN
dbo.OperatorLog ON DAY(dbo.PABXLog.CallDateTime) = DAY(dbo.OperatorLog.CallDateTime) AND MONTH(dbo.PABXLog.CallDateTime)
= MONTH(dbo.OperatorLog.CallDateTime) AND YEAR(dbo.PABXLog.CallDateTime) = YEAR(dbo.OperatorLog.CallDateTime) AND
dbo.PABXLog.Duration = dbo.OperatorLog.Duration AND dbo.PABXLog.CallType = dbo.OperatorLog.CallType AND
dbo.PABXLog.Location = dbo.OperatorLog.Location AND dbo.PABXLog.PhoneNumber = dbo.OperatorLog.PhoneNumber
WHERE (dbo.OperatorLog.PhoneNumber IS NULL)

- All matching rows :

Code Snippet

SELECT dbo.OperatorLog.PhoneNumber, dbo.OperatorLog.Location, dbo.OperatorLog.CallType, dbo.OperatorLog.Duration, dbo.OperatorLog.CallDateTime
FROM dbo.OperatorLog LEFT OUTER JOIN
dbo.PABXLog ON DAY(dbo.OperatorLog.CallDateTime) = DAY(dbo.PABXLog.CallDateTime) AND MONTH(dbo.OperatorLog.CallDateTime)
= MONTH(dbo.PABXLog.CallDateTime) AND YEAR(dbo.OperatorLog.CallDateTime) = YEAR(dbo.PABXLog.CallDateTime) AND
dbo.OperatorLog.Duration = dbo.PABXLog.Duration AND dbo.OperatorLog.CallType = dbo.PABXLog.CallType AND
dbo.OperatorLog.Location = dbo.PABXLog.Location AND dbo.OperatorLog.PhoneNumber = dbo.PABXLog.PhoneNumber

---

HTH.

Hayder Marzouk

|||

Thanks

Yes...you're correct

I did something similar...I'm gonna test it tomorrow and give you the answer..tahnks a lot

|||OK thanks...Did'nt work for all registries but it's a good beginning...