question

Sami avatar image
Sami asked

Complex Query that Meets several conditions

Hello There,

Well basically i was really looking forward to run a complex query to accomplish a task.

Let me first explain you the scenario.

I have two tables namely CDR_A and CDR_B respectively. Each of them has four fields, as follows:
Date_A, Time_A, PhoneNumber_A and Duration_A,
Date_B, Time_B, PhoneNumber_B and Duration_B
Well now i want to join all of them side by side like

Select CDR_A.Date_A, CDR_A.Time_A, CDR_A.PhoneNumber_A, CDR_A.Duration_A,  
CDR_B.Date_B, CDR_B.Time_B, CDR_B.PhoneNumber_B, CDR_B.Duration_B

the problem is that CDRs from table A and Table B are from two different time zone; Time_A is in GMT 0 and Time_B is in GMT 6 , and the PhoneNumbers occurs multiple times.

i am providing some sample data as follows:

DATA from Table CDR_A Date_A
Time _A PhoneNumber_A Duration_A
1-Feb-10 14:45:00 1196025335 85
1-Feb-10 14:47:00 1614169833 11
1-Feb-10 14:49:00 1730330005 23
1-Feb-10 14:51:00 1711563815 64
1-Feb-10 14:46:00 1196025335 45
1-Feb-10 14:55:00 1614169833 6
1-Feb-10 14:57:00 1196025335 96
1-Feb-10 14:59:00 28920473 45
1-Feb-10 15:01:00 1819181324 49
1-Feb-10 15:03:00 1952233558 16
1-Feb-10 15:05:00 1614169833 90
1-Feb-10 15:07:00 98918325 51
1-Feb-10 15:09:00 1732330080 10
1-Feb-10 15:11:00 1196025335 75

DATA from Table CDR_B
Date_B Time _B PhoneNumber_B Duration_B
1-Feb-10 20:47:00 1196025335 84
1-Feb-10 20:49:00 1614169833 10
1-Feb-10 20:51:00 1732330080 70
1-Feb-10 20:53:00 1196025335 45
1-Feb-10 20:48:00 1614169833 5
1-Feb-10 20:57:00 28920473 45
1-Feb-10 20:59:00 1711563815 40
1-Feb-10 21:01:00 1196025335 95
1-Feb-10 21:03:00 98918325 51
1-Feb-10 21:05:00 1952233558 15
1-Feb-10 21:07:00 1614169833 89
1-Feb-10 21:09:00 1819181324 56
1-Feb-10 21:11:00 1196025335 74
1-Feb-10 21:13:00 1730330005 69

Some key note: First the match will be based on Phone number column, then it will check if the number exists in more then once, if so, then it will match the time and date criteria. Well i want all the data from table A and the corresponding values for those data from table B, if suppose a Phone number from Table A was not found in table B then it would return a blank line or saying phonenumber not found.

please check the phone number is 1819181324, this number is available in table B but if you look at the time then you will be convinced to know that this not the call i was looking for. why? because it does not fall in the 5 minute range.

please check ,the number 1614169833 at time 14:46, the previous call for same number was at 14:45 so the both number was found in Table B, at 20:53 & 20:47 respectively but which number to choose? then it will fetch the closest match by looking at the duration.

i am Explaining my business sceneryo for your convenience:

I work in a Telco Company named NovoTel. I have a telephone switch and receive call from Sido who happens to be another company

Sami, a friend of Tom makes a call from Bangladesh to USA Phone number of sami (Calling Party) is call A# and is 123456789 Phone number of Tom (Called Party is call B# & is 1196025335 Now when a call is established and done, a file called Call Detail Record or CDR in short is produced at both Sido Switch and Novo Switch

This CDR usually contains a few information like A#, B# (PhoneNumber), Calling time , Calling date, Call Duration

As Sido is sending this call of sami to Novo, it means it is using Novo's Network, as a result, it must pay a portion of money to Novo based on how much duration sami has talked. So it is very important that both Swich comply the same duration

But in reality it does not happen, as there is network delay and other factors, then both the switch may have different local time and often there are issues like Novo's Switch says call to exist but Sido deny's that call and they raise a dispute.

That’s why it becomes essential to match these CDR side by side to identify which calls duration is not matching between Novo and Sido.

I hope you have understood the business scenery

Hope this bit of information would come in handy. Thank you once again for your kind help.

So Can i accomplish this using a query?

really Really Need your Help.

Regards, Sami

query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jason Crider avatar image
Jason Crider answered

There's no way I can write that whole thing for you, but here's a couple of thoughts.

I've used the conversion like this:

SELECT DATEADD(hour, -5, GETUTCDATE())

And also like this:

SELECT TOP 100
            a.ID,
            DATEADD(hour, -5, a.UTCCollectionDateTime)
    FROM dbo.ServerStatistics a
            INNER JOIN dbo.ServerActivity b
            ON a.SQLServerID = b.SQLServerID
    WHERE a.SQLServerID = '1'  
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CirqueDeSQLeil avatar image
CirqueDeSQLeil answered

Not the entire query either but just another idea on a direction to take.

with paredown as (
    Select Date_B, Time_B, PhoneNumber_B, Duration_B
            ,row_number() over (partition by phonenumber_b order by date_b desc) as RowID
        From CDR_B
)

Select A.Date_A,A.Time_A, A.PhoneNumber_A, A.Duration_A,  
            b.Date_B, b.Time_B, b.PhoneNumber_B, b.Duration_B
    From cdr_a A
        Inner Join PareDown B
            On A.Phonenumber_A = b.phonenumber_b
            And b.Rowid = 1
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.