February 8, 2011 at 9:39 am
I have data from a phone system that is dumped into an SQL table. The phone calls come in two parts. We get a record when the call is established and another record when the call is dropped.
I need to associate the established record with the dropped record. There are not any IDs that can be used to join the records. The dropped record is going to be the next log record chronologically for the same trunk. Also the first record has a call_type of 'A' and the matching second record will have a call_type of 'N'
I need to join these two records for a report and can really use come help putting together the SQL.
CREATE TABLE [dbo].[PBXDATA](
[ID] [decimal](10, 0) IDENTITY(1,1) NOT NULL,
[CALL_TIME] [datetime] NULL,
[DURATION] [datetime] NULL,
[DURATION_S] [int] NULL,
[DIRECTION] [nvarchar](4) NULL,
[CALL_TYPE] [nvarchar](8) NULL,
[CALLER_PHONE] [nvarchar](40) NULL,
[DIALED_PHONE] [nvarchar](40) NULL,
[TRUNK] [nvarchar](16) NULL,
[CO] [nvarchar](10) NULL,
[ACC] [nvarchar](16) NULL,
[EXT] [nvarchar](10) NULL,
) ON [PRIMARY]
Sample Code
[font="Courier New"]
ID CALL_TIME DURATION DURATION_S DIRECTION CALL_TYPE CALLER_PHONE DIALED_PHONE TRUNK CO ACC EXT
--------------------------------------- ----------------------- ----------------------- ----------- --------- --------- ---------------------------------------- ---------------------------------------- ---------------- ---------- ---------------- ----------
1 2011-02-08 07:47:49.000 1899-12-30 00:00:44.000 44 NULL S 954XXXXXXXXXXXXXX T00400101 NULL NULL DN224
2 2011-02-08 07:49:02.000 1899-12-30 00:00:29.000 29 NULL E DN225 NULL NULL T00400101
3 2011-02-08 07:59:42.000 1899-12-30 00:00:00.000 0 NULL A 152138 DN336 NULL NULL T00500123
4 2011-02-08 08:03:05.000 1899-12-30 00:03:16.000 196 NULL N 1561XXXXXXX DN336 NULL NULL A00500123
5 2011-02-08 08:07:35.000 1899-12-30 00:00:00.000 0 NULL A 555118 DN362 NULL NULL T00500123
6 2011-02-08 08:08:33.000 1899-12-30 00:00:00.000 0 NULL A 156015 DN336 NULL NULL T00500122
7 2011-02-08 08:10:58.000 1899-12-30 00:02:55.000 175 NULL N 1561XXXXXXX DN362 NULL NULL A00500123
8 2011-02-08 08:12:24.000 1899-12-30 00:03:46.000 226 NULL N 1561XXXXXXX DN336 NULL NULL A00500122
9 2011-02-08 08:28:47.000 1899-12-30 00:00:00.000 0 NULL A 152148 DN319 NULL NULL T00500123
10 2011-02-08 08:29:57.000 1899-12-30 00:01:03.000 63 NULL N 1561XXXXXXX DN319 NULL NULL A00500123
[/font]
February 8, 2011 at 10:42 am
In the simplest sense you need to construct a single record from two separate records.
One way to approach it would be to group each (A records and N records) by trunk, ordered by time. You could create an identity or just use ROW_NUMBER int a virtual table, temp table or CTE to associate the A with the N for each trunk.
Another option is to create a CTE grouped by trunk and ordered by time and row_number the whole thing. Then the matching N record to an A record becomes the Row_number + 1.
The probability of survival is inversely proportional to the angle of arrival.
February 8, 2011 at 1:13 pm
I was hoping to create a join with a subquery but I run into the problem that there are may records matching the trunk and I need only the record that matches that occurs just after the originate record.
I know I can do it with a cursor but I prefer to stay away from them.
February 10, 2011 at 8:22 am
Gary Herbstman (2/8/2011)
I know I can do it with a cursor but I prefer to stay away from them.
It can be done without a cursor. Try one of the approaches I suggested earlier and if you are having difficulty post the SQL of your attempt and we'll try to assist you further.
The probability of survival is inversely proportional to the angle of arrival.
February 10, 2011 at 9:13 am
Thanks but I went with a cron job and a cursor to traverse the records and populated a parent child set of columns to make reporting quick.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply