Help to build a SQL query in CDR

Answered Question
Sep 17th, 2007
User Badges:

I need to do a sql query that shows me calls receveid for a dial number and were transfered from another dial number in a determinate date and time.

Can anybody help me?.

Here's my basic "everything to and from a given number" query. This is at least a starting point for you.


SELECT DATEADD([second], dateTimeOrigination - 32400, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS Called_At, callingPartyNumber AS Extension,

originalCalledPartyNumber, finalCalledPartyNumber, DATEADD([second], dateTimeConnect - 32400, CONVERT(DATETIME, '1970-01-01 00:00:00', 102))

AS TimeConnect, DATEADD([second], dateTimeDisconnect - 32400, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS Time_Disconnect, duration,

callingPartyNumberPartition, originalCalledPartyNumberPartition

FROM CallDetailRecord

WHERE (originalCalledPartyNumber = '12345678') OR

(callingPartyNumber = '12345678')

ORDER BY dateTimeOrigination DESC


  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 3.5 (2 ratings)
Loading.
pklos Mon, 09/17/2007 - 08:05
User Badges:
  • Silver, 250 points or more

If this call is transfered from another number you can find it by filtering on lastRedirectDn field.


--

regards,

pk

mirtenmous Mon, 09/17/2007 - 23:35
User Badges:

I think it is not a good answer for my knowledge building SQL queries.

Thanks.

Correct Answer

Here's my basic "everything to and from a given number" query. This is at least a starting point for you.


SELECT DATEADD([second], dateTimeOrigination - 32400, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS Called_At, callingPartyNumber AS Extension,

originalCalledPartyNumber, finalCalledPartyNumber, DATEADD([second], dateTimeConnect - 32400, CONVERT(DATETIME, '1970-01-01 00:00:00', 102))

AS TimeConnect, DATEADD([second], dateTimeDisconnect - 32400, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS Time_Disconnect, duration,

callingPartyNumberPartition, originalCalledPartyNumberPartition

FROM CallDetailRecord

WHERE (originalCalledPartyNumber = '12345678') OR

(callingPartyNumber = '12345678')

ORDER BY dateTimeOrigination DESC


Actions

This Discussion