June 29, 2012 at 1:54 pm
rjbirkett (6/29/2012)
Yes, it does work. I think I get the picture. Use the script to put the data into dbo.CDR directly, dump all my useless code, and have access pull reports from that database. The data "looks" OK, I'm just wondering if dbo.CDR will accept it as is or if I should create a new table and see what field typoes SQL arbitrarily designates. You should see what it does if you try to move an Access table into SQL. All kinds of crazy s*&^ happens:-)Anyway. I think you have done enough already. I'm sure you have better things to do than waste time talking to an idiot. I have really enjoyed this. Odd I know, but learning is always fun for me. Have a great weekend. I will let you know how I get on.
Robert.
Please note, the only columns that are not being returned as strings at this time are the three datetime columns. I kept these three columns as one instead of spliting the date and time parts into separate columns and I made them datetime date types. There is more work to be done on this to get the other columns to the appropriate data types.
Also, Craig did an awesome job describing what I have been doing, and the difference between RBAR and set-based processing.
June 29, 2012 at 2:01 pm
Craig,
I don't know what to say. Brilliant explanation. If you could see my code you would know why I understand RBAR:-) There are really only two tables in my DB, the raw syslog feed and dbo.CDR where I put the processed data. Here is what comes in
"|CALL_END |20 |0 |0 |21 |236 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |7152753911 |7152753911 |0 |0 |7260 |7260 |99 |g711Ulaw64k |20 |10.207.0.14 |8284 |IP |GWAPP_NORMAL_CALL_CLEAR |0 |3119 |4974 |16 |-1 |3807383372162012121829@10.207.60.2 | 12:18:29.000 UTC Jun 21 2012 | 12:18:29.000 UTC Jun 21 2012 | 12:20:09.000 UTC Jun 21 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 12:20:09]"
"|CALL_END |10 |0 |0 |11 |246 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |7153409970 |7153409970 |0 |0 |7260 |7260 |304 |g711Ulaw64k |20 |10.207.0.13 |6790 |IP |GWAPP_NORMAL_CALL_CLEAR |0 |11162 |15244 |46 |-1 |1298870198216201213248@10.207.60.2 | 13:02:48.000 UTC Jun 21 2012 | 13:02:48.000 UTC Jun 21 2012 | 13:07:53.000 UTC Jun 21 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 13:07:53]"
"|CALL_END |18 |0 |0 |19 |239 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |7152981206 |7152981206 |0 |0 |7261 |7261 |311 |g711Ulaw64k |20 |10.207.0.13 |8246 |TEL |GWAPP_NORMAL_CALL_CLEAR |0 |4176 |15553 |16 |-1 |8840192792162012151232@10.207.60.2 | 15:12:32.000 UTC Jun 21 2012 | 15:12:32.000 UTC Jun 21 2012 | 15:17:43.000 UTC Jun 21 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 15:17:43]"
"|CALL_END |33 |0 |1 |10 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |7154821626 |7154821626 |0 |0 |7240 |7240 |129 |g711Ulaw64k |20 |10.207.0.14 |7592 |TEL |GWAPP_NORMAL_CALL_CLEAR |0 |5449 |6479 |18 |-1 |62633635921620121179@10.207.60.2 | 11:07:09.000 UTC Jun 21 2012 | 11:07:09.000 UTC Jun 21 2012 | 11:09:19.000 UTC Jun 21 2012 |0 |4 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 11:09:19]"
"|CALL_END |18 |0 |0 |19 |252 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |9062659950 |9062659950 |0 |0 |7265 |7265 |209 |g711Ulaw64k |20 |10.207.0.13 |23018|IP |GWAPP_NORMAL_CALL_CLEAR |0 |5356 |10463 |25 |-1 |1533609249216201264116@10.207.60.2 | 06:41:16.000 UTC Jun 21 2012 | 06:41:16.000 UTC Jun 21 2012 | 06:44:45.000 UTC Jun 21 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 06:44:45]"
"|CALL_END |12 |0 |0 |13 |252 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |9209803252 |9209803252 |0 |0 |7260 |7260 |332 |g711Ulaw64k |20 |10.207.0.14 |25772|IP |GWAPP_NORMAL_CALL_CLEAR |0 |8898 |16615 |18 |-1 |117991158821620128950@10.207.60.2 | 08:09:50.000 UTC Jun 21 2012 | 08:09:50.000 UTC Jun 21 2012 | 08:15:23.000 UTC Jun 21 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 08:15:23]"
"|CALL_END |20 |0 |0 |21 |242 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |8153729226 |8153729226 |0 |0 |7260 |7260 |179 |g711Ulaw64k |20 |10.207.0.10 |49160|IP |GWAPP_NORMAL_CALL_CLEAR |0 |8964 |8990 |26 |-1 |2068615836216201264148@10.207.60.2 | 06:41:48.000 UTC Jun 21 2012 | 06:41:48.000 UTC Jun 21 2012 | 06:44:48.000 UTC Jun 21 2012 |129 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 06:44:48]"
"|CALL_END |15 |0 |0 |16 |240 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |7156235100 |7156235100 |0 |0 |7260 |7260 |232 |g711Ulaw64k |20 |10.207.0.14 |6104 |IP |GWAPP_NORMAL_CALL_CLEAR |0 |5073 |11602 |16 |-1 |903841573216201284626@10.207.60.2 | 08:46:26.000 UTC Jun 21 2012 | 08:46:26.000 UTC Jun 21 2012 | 08:50:18.000 UTC Jun 21 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 08:50:18]"
"|CALL_END |37 |0 |1 |14 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |9202560577 |9202560577 |0 |0 |7240 |7240 |536 |g711Ulaw64k |20 |10.207.0.13 |25642|IP |GWAPP_NORMAL_CALL_CLEAR |0 |24164 |26835 |71 |-1 |969116746216201211940@10.207.60.2 | 11:09:40.000 UTC Jun 21 2012 | 11:09:40.000 UTC Jun 21 2012 | 11:18:37.000 UTC Jun 21 2012 |0 |4 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 11:18:37]"
And this is what my code produces in Access before I pump it back in. Note the split time/dates entries (yes, I do really need them:-).
"CALL_END",20,0,0,21,236,2,"ISDN","TEL",10.207.60.2,10.207.0.10,2,1,"7152753911","7152753911",0,0,"7260","7260",99,"g711Ulaw64k",20,10.207.0.14,8284,"IP","GWAPP_NORMAL_CALL_CLEAR",0,3119,4974,16,-1,"3807383372162012121829@10.207.60.2",12:18:29,6/21/2012,12:18:29,6/21/2012,12:20:09,6/21/2012,0,0,0,0,-1,0,0
"CALL_END",10,0,0,11,246,2,"ISDN","TEL",10.207.60.2,10.207.0.10,2,1,"7153409970","7153409970",0,0,"7260","7260",304,"g711Ulaw64k",20,10.207.0.13,6790,"IP","GWAPP_NORMAL_CALL_CLEAR",0,11162,15244,46,-1,"1298870198216201213248@10.207.60.2",13:02:48,6/21/2012,13:02:48,6/21/2012,13:07:53,6/21/2012,0,0,0,0,-1,0,0
"CALL_END",18,0,0,19,239,2,"ISDN","TEL",10.207.60.2,10.207.0.10,2,1,"7152981206","7152981206",0,0,"7261","7261",311,"g711Ulaw64k",20,10.207.0.13,8246,"TEL","GWAPP_NORMAL_CALL_CLEAR",0,4176,15553,16,-1,"8840192792162012151232@10.207.60.2",15:12:32,6/21/2012,15:12:32,6/21/2012,15:17:43,6/21/2012,0,0,0,0,-1,0,0
"CALL_END",33,0,1,10,255,2,"ISDN","TEL",10.207.60.2,10.207.0.10,2,1,"7154821626","7154821626",0,0,"7240","7240",129,"g711Ulaw64k",20,10.207.0.14,7592,"TEL","GWAPP_NORMAL_CALL_CLEAR",0,5449,6479,18,-1,"62633635921620121179@10.207.60.2",11:07:09,6/21/2012,11:07:09,6/21/2012,11:09:19,6/21/2012,0,4,0,0,-1,0,0
"CALL_END",18,0,0,19,252,2,"ISDN","TEL",10.207.60.2,10.207.0.10,2,1,"9062659950","9062659950",0,0,"7265","7265",209,"g711Ulaw64k",20,10.207.0.13,23018,"IP","GWAPP_NORMAL_CALL_CLEAR",0,5356,10463,25,-1,"1533609249216201264116@10.207.60.2",06:41:16,6/21/2012,06:41:16,6/21/2012,06:44:45,6/21/2012,0,0,0,0,-1,0,0
"CALL_END",12,0,0,13,252,2,"ISDN","TEL",10.207.60.2,10.207.0.10,2,1,"9209803252","9209803252",0,0,"7260","7260",332,"g711Ulaw64k",20,10.207.0.14,25772,"IP","GWAPP_NORMAL_CALL_CLEAR",0,8898,16615,18,-1,"117991158821620128950@10.207.60.2",08:09:50,6/21/2012,08:09:50,6/21/2012,08:15:23,6/21/2012,0,0,0,0,-1,0,0
"CALL_END",20,0,0,21,242,2,"ISDN","TEL",10.207.60.2,10.207.0.10,2,1,"8153729226","8153729226",0,0,"7260","7260",179,"g711Ulaw64k",20,10.207.0.10,49160,"IP","GWAPP_NORMAL_CALL_CLEAR",0,8964,8990,26,-1,"2068615836216201264148@10.207.60.2",06:41:48,6/21/2012,06:41:48,6/21/2012,06:44:48,6/21/2012,129,0,0,0,-1,0,0
After your explanantion I'm a lot better off. This is harder than I thought, and now I know why CDR applications are expensive!
Rob
June 29, 2012 at 2:07 pm
All, just so you know this is not a waste of your time, I work for an energy company. CDR is important because in the event of a gas leak/explosion/death that involves members of the public reporting a leak etc, we have to be (legally) able to say we received a call and someone at the emergency dispatch center answered it. There is no current CDR system for IP Mediant Gateways hence my design for the syslog feed into SQL server, the root cause of all this:-) I really do appreciate the help, I can't thank you enough. With all these skills, maybe you should write a commercial app to handle this type of stuff. mediant Gateways are everywhere IP telephony is.
Rob
June 29, 2012 at 2:08 pm
Not hard to split the date and time parts, but for now we can leave them alone. What I would do now is create a staging table based on what I have done so far so that you can change my current query from a simple select into an insert into query. In fact, we could make it a SELECT INTO query and let it create the initial staging table for you play with the data.
Just so you can see, here it is:
with SampleData as (
select
MsgText as SampleRec
from
dbo.Syslogd
where
MsgDate = dateadd(dd, -1, cast(getdate() as date))
)
select
--sd.SampleRec,
--max(case ds.ItemNumber
--when 1 then ds.Item else null end),
max(case ds.ItemNumber
when 2 then ds.Item else null end) [call_status],
max(case ds.ItemNumber
when 3 then ds.Item else null end) [channel_num],
max(case ds.ItemNumber
when 4 then ds.Item else null end) [sipcall_ident],
max(case ds.ItemNumber
when 5 then ds.Item else null end) [trunk_num],
max(case ds.ItemNumber
when 6 then ds.Item else null end) [b_channel],
max(case ds.ItemNumber
when 7 then ds.Item else null end) [sipconf_id],
max(case ds.ItemNumber
when 8 then ds.Item else null end) [trunk_group_num],
max(case ds.ItemNumber
when 9 then ds.Item else null end) [endpoint_type],
max(case ds.ItemNumber
when 10 then ds.Item else null end) [call_originator],
max(case ds.ItemNumber
when 11 then ds.Item else null end) [source_IP],
max(case ds.ItemNumber
when 12 then ds.Item else null end) [destination_IP],
max(case ds.ItemNumber
when 13 then ds.Item else null end) [sourcephone_numtype],
max(case ds.ItemNumber
when 14 then ds.Item else null end) [sourcephone_numplan],
max(case ds.ItemNumber
when 15 then ds.Item else null end) [sourcephone_number],
max(case ds.ItemNumber
when 16 then ds.Item else null end) [sourcephone_nummap],
max(case ds.ItemNumber
when 17 then ds.Item else null end) [destphone_numtype],
max(case ds.ItemNumber
when 18 then ds.Item else null end) [destphone_numplan],
max(case ds.ItemNumber
when 19 then ds.Item else null end) [destphone_number],
max(case ds.ItemNumber
when 20 then ds.Item else null end) [destphone_nummap],
max(case ds.ItemNumber
when 21 then ds.Item else null end) [call_duration],
max(case ds.ItemNumber
when 22 then ds.Item else null end) [call_codec],
max(case ds.ItemNumber
when 23 then ds.Item else null end) [packet_intervall],
max(case ds.ItemNumber
when 24 then ds.Item else null end) [rtp_IP],
max(case ds.ItemNumber
when 25 then ds.Item else null end) [rtp_port],
max(case ds.ItemNumber
when 26 then ds.Item else null end) [call_release],
max(case ds.ItemNumber
when 27 then ds.Item else null end) [termination_reason],
max(case ds.ItemNumber
when 28 then ds.Item else null end) [fax_data],
max(case ds.ItemNumber
when 29 then ds.Item else null end) [packets_in],
max(case ds.ItemNumber
when 30 then ds.Item else null end) [packets_out],
max(case ds.ItemNumber
when 31 then ds.Item else null end) [packets_lost],
max(case ds.ItemNumber
when 32 then ds.Item else null end) [header_id],
max(case ds.ItemNumber
when 33 then ds.Item else null end) [rtp_id],
max(case ds.ItemNumber
when 34 then convert(datetime,replace(substring(right(ds.Item, 12),5,3) + left(right(ds.Item, 12),4) + right(right(ds.Item, 12),5) + ' ' + left(ds.Item, 13),'.',':'),113) else null end) [callsetup_datetime],
max(case ds.ItemNumber
when 35 then convert(datetime,replace(substring(right(ds.Item, 12),5,3) + left(right(ds.Item, 12),4) + right(right(ds.Item, 12),5) + ' ' + left(ds.Item, 13),'.',':'),113) else null end) [callconnect_datetime],
max(case ds.ItemNumber
when 36 then convert(datetime,replace(substring(right(ds.Item, 12),5,3) + left(right(ds.Item, 12),4) + right(right(ds.Item, 12),5) + ' ' + left(ds.Item, 13),'.',':'),113) else null end) [callrelease_datetime],
max(case ds.ItemNumber
when 37 then ds.Item else null end) [rtp_delay],
max(case ds.ItemNumber
when 38 then ds.Item else null end) [rtp_jitter],
max(case ds.ItemNumber
when 39 then ds.Item else null end) [localrtp_source],
max(case ds.ItemNumber
when 40 then ds.Item else null end) [remotertp_source],
max(case ds.ItemNumber
when 41 then ds.Item else null end) [redirect_reason],
max(case ds.ItemNumber
when 42 then ds.Item else null end) [redirect_number],
max(case ds.ItemNumber
when 43 then ds.Item else null end) [redirect_numplan]--,
--max(case ds.ItemNumber
--when 44 then ds.Item else null end),
--max(case ds.ItemNumber
--when 45 then ds.Item else null end)
into
dbo.CDRStaging
from
SampleData sd
cross apply dbo.DelimitedSplit8K(SampleRec,'|') ds
group by
sd.SampleRec;
June 29, 2012 at 2:16 pm
You beat me to it. I was modifying your script to create the date/time pieces of the puzzle. I have added the details of the other table since there are only two of them. This is where MY finished RBAR data goes:-) If your script creates a temporary table, what column structure does it use? Is it text or arbitrary? Anyway, I am in the undesirable position of having to drive to O'Hare to pick up my colleauge from the airport. I hate to leave this where it is but I will take a look when I get back. As I said, I have all weekend to keep a low profile while everyone else celbrates their independance from people like me:-)
/****** Object: Table [dbo].[CDR] Script Date: 06/29/2012 15:09:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CDR](
[call_status] [nvarchar](255) NULL,
[channel_num] [int] NULL,
[sipcall_ident] [int] NULL,
[trunk_num] [int] NULL,
[b_channel] [int] NULL,
[sipconf_id] [int] NULL,
[trunk_group_num] [int] NULL,
[endpoint_type] [nvarchar](255) NULL,
[call_originator] [nvarchar](255) NULL,
[source_IP] [nvarchar](255) NULL,
[destination_IP] [nvarchar](255) NULL,
[sourcephone_numtype] [int] NULL,
[sourcephone_numplan] [int] NULL,
[sourcephone_number] [nvarchar](255) NULL,
[sourcephone_nummap] [nvarchar](255) NULL,
[destphone_numtype] [int] NULL,
[destphone_numplan] [int] NULL,
[destphone_number] [nvarchar](255) NULL,
[destphone_nummap] [nvarchar](255) NULL,
[call_duration] [int] NULL,
[call_codec] [nvarchar](255) NULL,
[packet_intervall] [int] NULL,
[rtp_IP] [nvarchar](255) NULL,
[rtp_port] [int] NULL,
[call_release] [nvarchar](255) NULL,
[termination_reason] [nvarchar](255) NULL,
[fax_data] [int] NULL,
[packets_in] [int] NULL,
[packets_out] [int] NULL,
[packets_lost] [int] NULL,
[header_id] [int] NULL,
[rtp_id] [nvarchar](255) NULL,
[callsetup_time] [time](7) NULL,
[callsetup_date] [date] NULL,
[callconnect_time] [time](7) NULL,
[callconnect_date] [date] NULL,
[callrelease_time] [time](7) NULL,
[callrelease_date] [date] NULL,
[rtp_delay] [int] NULL,
[rtp_jitter] [int] NULL,
[localrtp_source] [int] NULL,
[remotertp_source] [int] NULL,
[redirect_reason] [int] NULL,
[redirect_number] [int] NULL,
[redirect_numplan] [int] NULL
) ON [PRIMARY]
GO
Rob
June 29, 2012 at 2:20 pm
All columns at this time are varchar columns with the exception of the three datetime columns. Those are just that, datetime columns. You may find it easier to load the data as is to a staging table. Then it is a simple matter of selecting the data from that table doing the necessary data conversions to load your final target table. It will also be easier to split the datetime columns at that point as well.
June 29, 2012 at 2:31 pm
Just for example, I went back to my two record sample. Please check out how the staging table is created and then the select from it where I split out the dates and times into separate columns.
USE [SandBox]
GO
/****** Object: Table [dbo].[CDRStaging] Script Date: 06/29/2012 14:27:48 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CDRStaging]') AND type in (N'U'))
DROP TABLE [dbo].[CDRStaging]
GO
with SampleData as (
select * from (VALUES (
'|CALL_END |20 |0 |0 |21 |236 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |7152753911 |7152753911 |0 |0 |7260 |7260 |99 |g711Ulaw64k |20 |10.207.0.14 |8284 |IP |GWAPP_NORMAL_CALL_CLEAR |0 |3119 |4974 |16 |-1 |3807383372162012121829@10.207.60.2 | 12:18:29.000 UTC Jun 21 2012 | 12:18:29.000 UTC Jun 21 2012 | 12:20:09.000 UTC Jun 21 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 12:20:09]'),(
'|CALL_END |20 |0 |0 |21 |236 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |7152753911 |7152753911 |0 |0 |7260 |7260 |99 |g711Ulaw64k |20 |10.207.0.14 |8284 |IP |GWAPP_NORMAL_CALL_CLEAR |0 |3119 |4974 |16 |-1 |3807383372162012121829@10.207.60.2 | 12:18:29.000 UTC Jun 22 2012 | 12:18:29.000 UTC Jun 22 2012 | 12:20:09.000 UTC Jun 22 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 12:20:09]'))dt(SampleRec))
select
--sd.SampleRec,
--max(case ds.ItemNumber
--when 1 then ds.Item else null end),
max(case ds.ItemNumber
when 2 then ds.Item else null end) [call_status],
max(case ds.ItemNumber
when 3 then ds.Item else null end) [channel_num],
max(case ds.ItemNumber
when 4 then ds.Item else null end) [sipcall_ident],
max(case ds.ItemNumber
when 5 then ds.Item else null end) [trunk_num],
max(case ds.ItemNumber
when 6 then ds.Item else null end) [b_channel],
max(case ds.ItemNumber
when 7 then ds.Item else null end) [sipconf_id],
max(case ds.ItemNumber
when 8 then ds.Item else null end) [trunk_group_num],
max(case ds.ItemNumber
when 9 then ds.Item else null end) [endpoint_type],
max(case ds.ItemNumber
when 10 then ds.Item else null end) [call_originator],
max(case ds.ItemNumber
when 11 then ds.Item else null end) [source_IP],
max(case ds.ItemNumber
when 12 then ds.Item else null end) [destination_IP],
max(case ds.ItemNumber
when 13 then ds.Item else null end) [sourcephone_numtype],
max(case ds.ItemNumber
when 14 then ds.Item else null end) [sourcephone_numplan],
max(case ds.ItemNumber
when 15 then ds.Item else null end) [sourcephone_number],
max(case ds.ItemNumber
when 16 then ds.Item else null end) [sourcephone_nummap],
max(case ds.ItemNumber
when 17 then ds.Item else null end) [destphone_numtype],
max(case ds.ItemNumber
when 18 then ds.Item else null end) [destphone_numplan],
max(case ds.ItemNumber
when 19 then ds.Item else null end) [destphone_number],
max(case ds.ItemNumber
when 20 then ds.Item else null end) [destphone_nummap],
max(case ds.ItemNumber
when 21 then ds.Item else null end) [call_duration],
max(case ds.ItemNumber
when 22 then ds.Item else null end) [call_codec],
max(case ds.ItemNumber
when 23 then ds.Item else null end) [packet_intervall],
max(case ds.ItemNumber
when 24 then ds.Item else null end) [rtp_IP],
max(case ds.ItemNumber
when 25 then ds.Item else null end) [rtp_port],
max(case ds.ItemNumber
when 26 then ds.Item else null end) [call_release],
max(case ds.ItemNumber
when 27 then ds.Item else null end) [termination_reason],
max(case ds.ItemNumber
when 28 then ds.Item else null end) [fax_data],
max(case ds.ItemNumber
when 29 then ds.Item else null end) [packets_in],
max(case ds.ItemNumber
when 30 then ds.Item else null end) [packets_out],
max(case ds.ItemNumber
when 31 then ds.Item else null end) [packets_lost],
max(case ds.ItemNumber
when 32 then ds.Item else null end) [header_id],
max(case ds.ItemNumber
when 33 then ds.Item else null end) [rtp_id],
max(case ds.ItemNumber
when 34 then convert(datetime,replace(substring(right(ds.Item, 12),5,3) + left(right(ds.Item, 12),4) + right(right(ds.Item, 12),5) + ' ' + left(ds.Item, 13),'.',':'),113) else null end) [callsetup_datetime],
max(case ds.ItemNumber
when 35 then convert(datetime,replace(substring(right(ds.Item, 12),5,3) + left(right(ds.Item, 12),4) + right(right(ds.Item, 12),5) + ' ' + left(ds.Item, 13),'.',':'),113) else null end) [callconnect_datetime],
max(case ds.ItemNumber
when 36 then convert(datetime,replace(substring(right(ds.Item, 12),5,3) + left(right(ds.Item, 12),4) + right(right(ds.Item, 12),5) + ' ' + left(ds.Item, 13),'.',':'),113) else null end) [callrelease_datetime],
max(case ds.ItemNumber
when 37 then ds.Item else null end) [rtp_delay],
max(case ds.ItemNumber
when 38 then ds.Item else null end) [rtp_jitter],
max(case ds.ItemNumber
when 39 then ds.Item else null end) [localrtp_source],
max(case ds.ItemNumber
when 40 then ds.Item else null end) [remotertp_source],
max(case ds.ItemNumber
when 41 then ds.Item else null end) [redirect_reason],
max(case ds.ItemNumber
when 42 then ds.Item else null end) [redirect_number],
max(case ds.ItemNumber
when 43 then ds.Item else null end) [redirect_numplan]--,
--max(case ds.ItemNumber
--when 44 then ds.Item else null end),
--max(case ds.ItemNumber
--when 45 then ds.Item else null end)
into
dbo.CDRStaging
from
SampleData sd
cross apply dbo.DelimitedSplit8K(SampleRec,'|') ds
group by
sd.SampleRec;
go
select
call_status,
channel_num,
sipcall_ident,
trunk_num,
b_channel,
sipconf_id,
trunk_group_num,
endpoint_type,
call_originator,
source_IP,
destination_IP,
sourcephone_numtype,
sourcephone_numplan,
sourcephone_number,
sourcephone_nummap,
destphone_numtype,
destphone_numplan,
destphone_number,
destphone_nummap,
call_duration,
call_codec,
packet_intervall,
rtp_IP,
rtp_port,
call_release,
termination_reason,
fax_data,
packets_in,
packets_out,
packets_lost,
header_id,
rtp_id,
cast(callsetup_datetime as time(3)) callsetup_time,
cast(callsetup_datetime as date) callsetup_date,
--callsetup_datetime,
cast(callconnect_datetime as time(3)) callconnect_time,
cast(callconnect_datetime as date) callconnect_date,
--callconnect_datetime,
cast(callrelease_datetime as time(3)) callrelease_time,
cast(callrelease_datetime as date) callrelease_date,
--callrelease_datetime,
rtp_delay,
rtp_jitter,
localrtp_source,
remotertp_source,
redirect_reason,
redirect_number,
redirect_numplan
from
dbo.CDRStaging;
go
June 29, 2012 at 2:43 pm
rjbirkett (6/29/2012)
Craig,I don't know what to say. Brilliant explanation. If you could see my code you would know why I understand RBAR:-) There are really only two tables in my DB, the raw syslog feed and dbo.CDR where I put the processed data. Here is what comes in
"|CALL_END |20 |0 |0 |21 |236 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |7152753911 |7152753911 |0 |0 |7260 |7260 |99 |g711Ulaw64k |20 |10.207.0.14 |8284 |IP |GWAPP_NORMAL_CALL_CLEAR |0 |3119 |4974 |16 |-1 |3807383372162012121829@10.207.60.2 | 12:18:29.000 UTC Jun 21 2012 | 12:18:29.000 UTC Jun 21 2012 | 12:20:09.000 UTC Jun 21 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 12:20:09]""|CALL_END |10 |0 |0 |11 |246 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |7153409970 |7153409970 |0 |0 |7260 |7260 |304 |g711Ulaw64k |20 |10.207.0.13 |6790 |IP |GWAPP_NORMAL_CALL_CLEAR |0 |11162 |15244 |46 |-1 |1298870198216201213248@10.207.60.2 | 13:02:48.000 UTC Jun 21 2012 | 13:02:48.000 UTC Jun 21 2012 | 13:07:53.000 UTC Jun 21 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 13:07:53]"
"|CALL_END |18 |0 |0 |19 |239 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |7152981206 |7152981206 |0 |0 |7261 |7261 |311 |g711Ulaw64k |20 |10.207.0.13 |8246 |TEL |GWAPP_NORMAL_CALL_CLEAR |0 |4176 |15553 |16 |-1 |8840192792162012151232@10.207.60.2 | 15:12:32.000 UTC Jun 21 2012 | 15:12:32.000 UTC Jun 21 2012 | 15:17:43.000 UTC Jun 21 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 15:17:43]"
"|CALL_END |33 |0 |1 |10 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |7154821626 |7154821626 |0 |0 |7240 |7240 |129 |g711Ulaw64k |20 |10.207.0.14 |7592 |TEL |GWAPP_NORMAL_CALL_CLEAR |0 |5449 |6479 |18 |-1 |62633635921620121179@10.207.60.2 | 11:07:09.000 UTC Jun 21 2012 | 11:07:09.000 UTC Jun 21 2012 | 11:09:19.000 UTC Jun 21 2012 |0 |4 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 11:09:19]"
"|CALL_END |18 |0 |0 |19 |252 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |9062659950 |9062659950 |0 |0 |7265 |7265 |209 |g711Ulaw64k |20 |10.207.0.13 |23018|IP |GWAPP_NORMAL_CALL_CLEAR |0 |5356 |10463 |25 |-1 |1533609249216201264116@10.207.60.2 | 06:41:16.000 UTC Jun 21 2012 | 06:41:16.000 UTC Jun 21 2012 | 06:44:45.000 UTC Jun 21 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 06:44:45]"
"|CALL_END |12 |0 |0 |13 |252 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |9209803252 |9209803252 |0 |0 |7260 |7260 |332 |g711Ulaw64k |20 |10.207.0.14 |25772|IP |GWAPP_NORMAL_CALL_CLEAR |0 |8898 |16615 |18 |-1 |117991158821620128950@10.207.60.2 | 08:09:50.000 UTC Jun 21 2012 | 08:09:50.000 UTC Jun 21 2012 | 08:15:23.000 UTC Jun 21 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 08:15:23]"
"|CALL_END |20 |0 |0 |21 |242 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |8153729226 |8153729226 |0 |0 |7260 |7260 |179 |g711Ulaw64k |20 |10.207.0.10 |49160|IP |GWAPP_NORMAL_CALL_CLEAR |0 |8964 |8990 |26 |-1 |2068615836216201264148@10.207.60.2 | 06:41:48.000 UTC Jun 21 2012 | 06:41:48.000 UTC Jun 21 2012 | 06:44:48.000 UTC Jun 21 2012 |129 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 06:44:48]"
"|CALL_END |15 |0 |0 |16 |240 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |7156235100 |7156235100 |0 |0 |7260 |7260 |232 |g711Ulaw64k |20 |10.207.0.14 |6104 |IP |GWAPP_NORMAL_CALL_CLEAR |0 |5073 |11602 |16 |-1 |903841573216201284626@10.207.60.2 | 08:46:26.000 UTC Jun 21 2012 | 08:46:26.000 UTC Jun 21 2012 | 08:50:18.000 UTC Jun 21 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 08:50:18]"
"|CALL_END |37 |0 |1 |14 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |9202560577 |9202560577 |0 |0 |7240 |7240 |536 |g711Ulaw64k |20 |10.207.0.13 |25642|IP |GWAPP_NORMAL_CALL_CLEAR |0 |24164 |26835 |71 |-1 |969116746216201211940@10.207.60.2 | 11:09:40.000 UTC Jun 21 2012 | 11:09:40.000 UTC Jun 21 2012 | 11:18:37.000 UTC Jun 21 2012 |0 |4 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 11:18:37]"
And this is what my code produces in Access before I pump it back in. Note the split time/dates entries (yes, I do really need them:-).
"CALL_END",20,0,0,21,236,2,"ISDN","TEL",10.207.60.2,10.207.0.10,2,1,"7152753911","7152753911",0,0,"7260","7260",99,"g711Ulaw64k",20,10.207.0.14,8284,"IP","GWAPP_NORMAL_CALL_CLEAR",0,3119,4974,16,-1,"3807383372162012121829@10.207.60.2",12:18:29,6/21/2012,12:18:29,6/21/2012,12:20:09,6/21/2012,0,0,0,0,-1,0,0"CALL_END",10,0,0,11,246,2,"ISDN","TEL",10.207.60.2,10.207.0.10,2,1,"7153409970","7153409970",0,0,"7260","7260",304,"g711Ulaw64k",20,10.207.0.13,6790,"IP","GWAPP_NORMAL_CALL_CLEAR",0,11162,15244,46,-1,"1298870198216201213248@10.207.60.2",13:02:48,6/21/2012,13:02:48,6/21/2012,13:07:53,6/21/2012,0,0,0,0,-1,0,0
"CALL_END",18,0,0,19,239,2,"ISDN","TEL",10.207.60.2,10.207.0.10,2,1,"7152981206","7152981206",0,0,"7261","7261",311,"g711Ulaw64k",20,10.207.0.13,8246,"TEL","GWAPP_NORMAL_CALL_CLEAR",0,4176,15553,16,-1,"8840192792162012151232@10.207.60.2",15:12:32,6/21/2012,15:12:32,6/21/2012,15:17:43,6/21/2012,0,0,0,0,-1,0,0
"CALL_END",33,0,1,10,255,2,"ISDN","TEL",10.207.60.2,10.207.0.10,2,1,"7154821626","7154821626",0,0,"7240","7240",129,"g711Ulaw64k",20,10.207.0.14,7592,"TEL","GWAPP_NORMAL_CALL_CLEAR",0,5449,6479,18,-1,"62633635921620121179@10.207.60.2",11:07:09,6/21/2012,11:07:09,6/21/2012,11:09:19,6/21/2012,0,4,0,0,-1,0,0
"CALL_END",18,0,0,19,252,2,"ISDN","TEL",10.207.60.2,10.207.0.10,2,1,"9062659950","9062659950",0,0,"7265","7265",209,"g711Ulaw64k",20,10.207.0.13,23018,"IP","GWAPP_NORMAL_CALL_CLEAR",0,5356,10463,25,-1,"1533609249216201264116@10.207.60.2",06:41:16,6/21/2012,06:41:16,6/21/2012,06:44:45,6/21/2012,0,0,0,0,-1,0,0
"CALL_END",12,0,0,13,252,2,"ISDN","TEL",10.207.60.2,10.207.0.10,2,1,"9209803252","9209803252",0,0,"7260","7260",332,"g711Ulaw64k",20,10.207.0.14,25772,"IP","GWAPP_NORMAL_CALL_CLEAR",0,8898,16615,18,-1,"117991158821620128950@10.207.60.2",08:09:50,6/21/2012,08:09:50,6/21/2012,08:15:23,6/21/2012,0,0,0,0,-1,0,0
"CALL_END",20,0,0,21,242,2,"ISDN","TEL",10.207.60.2,10.207.0.10,2,1,"8153729226","8153729226",0,0,"7260","7260",179,"g711Ulaw64k",20,10.207.0.10,49160,"IP","GWAPP_NORMAL_CALL_CLEAR",0,8964,8990,26,-1,"2068615836216201264148@10.207.60.2",06:41:48,6/21/2012,06:41:48,6/21/2012,06:44:48,6/21/2012,129,0,0,0,-1,0,0
After your explanantion I'm a lot better off. This is harder than I thought, and now I know why CDR applications are expensive!
Rob
I'm glad I could help. I'll do what I can to 'fill in' here in case Lynn gets busy but I've got something due this weekend that's eating my time and I've found that when trying to help out a new coder that a single voice can be more useful than us having slightly different viewpoints and the code getting confuzzled. Also, just a side note. To help with forum formatting when you have incredibly long lines like the above please use the [code= tags you'll see on the left. If you quote my message you'll see what I did to these. It just makes it a lot easier to read. Lynn's been doing it for the SQL code with the code="sql" tag. The above is just "plain".
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 29, 2012 at 2:44 pm
Sorry, duplicate post.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 29, 2012 at 2:49 pm
Evil Kraig F (6/29/2012)
Craig, feel free to jump in like you did with that wonderful explaination. Code wise, you may be right, we all code slightly differently, but if you see something that needs some explaination, please don't hesitate to say something.
I may fail to explain it or may not be clear in my explaination, and hearing from another person in a different way can be helpful.
June 29, 2012 at 3:08 pm
Lynn Pettis (6/29/2012)
Evil Kraig F (6/29/2012)
Craig, feel free to jump in like you did with that wonderful explaination. Code wise, you may be right, we all code slightly differently, but if you see something that needs some explaination, please don't hesitate to say something.
I may fail to explain it or may not be clear in my explaination, and hearing from another person in a different way can be helpful.
Yepyep, we're apples to apples on that, just different phrasings. 🙂 I just don't want to start adapting your code one way and you another and ending up with two incompatible code bases for some poor fella still getting the basics and we keep swapping snippets back and forth and lose 'em.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 29, 2012 at 3:22 pm
Evil Kraig F (6/29/2012)
Lynn Pettis (6/29/2012)
Evil Kraig F (6/29/2012)
Craig, feel free to jump in like you did with that wonderful explaination. Code wise, you may be right, we all code slightly differently, but if you see something that needs some explaination, please don't hesitate to say something.
I may fail to explain it or may not be clear in my explaination, and hearing from another person in a different way can be helpful.
Yepyep, we're apples to apples on that, just different phrasings. 🙂 I just don't want to start adapting your code one way and you another and ending up with two incompatible code bases for some poor fella still getting the basics and we keep swapping snippets back and forth and lose 'em.
Agreed.
June 29, 2012 at 3:23 pm
Once the data is in the staging table, the following code can be run to populate the final target table:
insert into dbo.CDR (
call_status,
channel_num,
sipcall_ident,
trunk_num,
b_channel,
sipconf_id,
trunk_group_num,
endpoint_type,
call_originator,
source_IP,
destination_IP,
sourcephone_numtype,
sourcephone_numplan,
sourcephone_number,
sourcephone_nummap,
destphone_numtype,
destphone_numplan,
destphone_number,
destphone_nummap,
call_duration,
call_codec,
packet_intervall,
rtp_IP,
rtp_port,
call_release,
termination_reason,
fax_data,
packets_in,
packets_out,
packets_lost,
header_id,
rtp_id,
callsetup_time,
callsetup_date,
callconnect_time,
callconnect_date,
callrelease_time,
callrelease_date,
rtp_delay,
rtp_jitter,
localrtp_source,
remotertp_source,
redirect_reason,
redirect_number,
redirect_numplan
)
select
cast(call_status as nvarchar(255)) call_status,
cast(channel_num as int) channel_num,
cast(sipcall_ident as int) sipcall_ident,
cast(trunk_num as int) trunk_num,
cast(b_channel as int) b_channel,
cast(sipconf_id as int) sipconf_id,
cast(trunk_group_num as int) trunk_group_num,
cast(endpoint_type as nvarchar(255)) endpoint_type,
cast(call_originator as nvarchar(255)) call_originator,
cast(source_IP as nvarchar(255)) source_IP,
cast(destination_IP as nvarchar(255)) destination_IP,
cast(sourcephone_numtype as int) sourcephone_numtype,
cast(sourcephone_numplan as int) sourcephone_numplan,
cast(sourcephone_number as nvarchar(255)) sourcephone_number,
cast(sourcephone_nummap as nvarchar(255)) sourcephone_nummap,
cast(destphone_numtype as int) destphone_numtype,
cast(destphone_numplan as int) destphone_numplan,
cast(destphone_number as nvarchar(255)) destphone_number,
cast(destphone_nummap as nvarchar(255)) destphone_nummap,
cast(call_duration as int) call_duration,
cast(call_codec as nvarchar(255)) call_codec,
cast(packet_intervall as int) packet_intervall,
cast(rtp_IP as nvarchar(255)) rtp_IP,
cast(rtp_port as int) rtp_port,
cast(call_release as nvarchar(255)) call_release,
cast(termination_reason as nvarchar(255)) termination_reason,
cast(fax_data as int) fax_data,
cast(packets_in as int) packets_in,
cast(packets_out as int) packets_out,
cast(packets_lost as int) packets_lost,
cast(header_id as int) header_id,
cast(rtp_id as nvarchar(255)) rtp_id,
cast(callsetup_datetime as time(7)) callsetup_time,
cast(callsetup_datetime as date) callsetup_date,
cast(callconnect_datetime as time(7)) callconnect_time,
cast(callconnect_datetime as date) callconnect_date,
cast(callrelease_datetime as time(7)) callrelease_time,
cast(callrelease_datetime as date) callrelease_date,
cast(rtp_delay as int) rtp_delay,
cast(rtp_jitter as int) rtp_jitter,
cast(localrtp_source as int) localrtp_source,
cast(remotertp_source as int) remotertp_source,
cast(redirect_reason as int) redirect_reason,
cast(redirect_number as int) redirect_number,
cast(redirect_numplan as int) redirect_numplan
from
dbo.CDRStaging;
go
June 29, 2012 at 6:22 pm
Lynn and Craig,
Sitting in traffic gives you pause for thought and time to think. I omitted the fact that SYSLOG gets all the syslog data from the gateway (mostly for troubleshooting purposes, we only keep the data for a week). So, when I run the code you so kindly provided it fails, because although we are extracting from SYSLOG by date, we get everything, so it fails. My bad. I forgot to mention I pull by a query that looks for MsgDate and MsgText containing "CALL_END" so I just get what I want. So,
where
MsgDate = dateadd(dd, -1, cast(getdate() as date)) needs to include an additional criteria for MsgText which would be INSTR(MsgText, "CALL_END") I assume.
I will see if I can figure that out for myself. While I was thinking about all this, I wondered if, since there are only 2000 calls a day to the call center, could I use a trigger to run a stored procedure to convert the record that sets off the trigger as it comes in to the syslog database and just keep adding records to the CDR table? Just a thought. I have written lots of simple stored procedures but never used triggers. Way beyond my capabilities at the moment. Everything works as tested with a couple of variables, so I have no doubt I can get it working with a weekend to spend on it. I appreciate all the help. Really. You guys have gone way beyond the call. if you ever need any help on Cisco LAN/WAN issues, I'm your man.
Rob
June 29, 2012 at 6:33 pm
Going to save you some time here:
with SampleData as (
select
MsgText as SampleRec
from
dbo.Syslogd
where
MsgDate = dateadd(dd, -1, cast(getdate() as date)) and
MsgText like '%CALL_END%'
)
select
--sd.SampleRec,
--max(case ds.ItemNumber
--when 1 then ds.Item else null end),
max(case ds.ItemNumber
when 2 then ds.Item else null end) [call_status],
max(case ds.ItemNumber
when 3 then ds.Item else null end) [channel_num],
max(case ds.ItemNumber
when 4 then ds.Item else null end) [sipcall_ident],
max(case ds.ItemNumber
when 5 then ds.Item else null end) [trunk_num],
max(case ds.ItemNumber
when 6 then ds.Item else null end) [b_channel],
max(case ds.ItemNumber
when 7 then ds.Item else null end) [sipconf_id],
max(case ds.ItemNumber
when 8 then ds.Item else null end) [trunk_group_num],
max(case ds.ItemNumber
when 9 then ds.Item else null end) [endpoint_type],
max(case ds.ItemNumber
when 10 then ds.Item else null end) [call_originator],
max(case ds.ItemNumber
when 11 then ds.Item else null end) [source_IP],
max(case ds.ItemNumber
when 12 then ds.Item else null end) [destination_IP],
max(case ds.ItemNumber
when 13 then ds.Item else null end) [sourcephone_numtype],
max(case ds.ItemNumber
when 14 then ds.Item else null end) [sourcephone_numplan],
max(case ds.ItemNumber
when 15 then ds.Item else null end) [sourcephone_number],
max(case ds.ItemNumber
when 16 then ds.Item else null end) [sourcephone_nummap],
max(case ds.ItemNumber
when 17 then ds.Item else null end) [destphone_numtype],
max(case ds.ItemNumber
when 18 then ds.Item else null end) [destphone_numplan],
max(case ds.ItemNumber
when 19 then ds.Item else null end) [destphone_number],
max(case ds.ItemNumber
when 20 then ds.Item else null end) [destphone_nummap],
max(case ds.ItemNumber
when 21 then ds.Item else null end) [call_duration],
max(case ds.ItemNumber
when 22 then ds.Item else null end) [call_codec],
max(case ds.ItemNumber
when 23 then ds.Item else null end) [packet_intervall],
max(case ds.ItemNumber
when 24 then ds.Item else null end) [rtp_IP],
max(case ds.ItemNumber
when 25 then ds.Item else null end) [rtp_port],
max(case ds.ItemNumber
when 26 then ds.Item else null end) [call_release],
max(case ds.ItemNumber
when 27 then ds.Item else null end) [termination_reason],
max(case ds.ItemNumber
when 28 then ds.Item else null end) [fax_data],
max(case ds.ItemNumber
when 29 then ds.Item else null end) [packets_in],
max(case ds.ItemNumber
when 30 then ds.Item else null end) [packets_out],
max(case ds.ItemNumber
when 31 then ds.Item else null end) [packets_lost],
max(case ds.ItemNumber
when 32 then ds.Item else null end) [header_id],
max(case ds.ItemNumber
when 33 then ds.Item else null end) [rtp_id],
max(case ds.ItemNumber
when 34 then convert(datetime,replace(substring(right(ds.Item, 12),5,3) + left(right(ds.Item, 12),4) + right(right(ds.Item, 12),5) + ' ' + left(ds.Item, 13),'.',':'),113) else null end) [callsetup_datetime],
max(case ds.ItemNumber
when 35 then convert(datetime,replace(substring(right(ds.Item, 12),5,3) + left(right(ds.Item, 12),4) + right(right(ds.Item, 12),5) + ' ' + left(ds.Item, 13),'.',':'),113) else null end) [callconnect_datetime],
max(case ds.ItemNumber
when 36 then convert(datetime,replace(substring(right(ds.Item, 12),5,3) + left(right(ds.Item, 12),4) + right(right(ds.Item, 12),5) + ' ' + left(ds.Item, 13),'.',':'),113) else null end) [callrelease_datetime],
max(case ds.ItemNumber
when 37 then ds.Item else null end) [rtp_delay],
max(case ds.ItemNumber
when 38 then ds.Item else null end) [rtp_jitter],
max(case ds.ItemNumber
when 39 then ds.Item else null end) [localrtp_source],
max(case ds.ItemNumber
when 40 then ds.Item else null end) [remotertp_source],
max(case ds.ItemNumber
when 41 then ds.Item else null end) [redirect_reason],
max(case ds.ItemNumber
when 42 then ds.Item else null end) [redirect_number],
max(case ds.ItemNumber
when 43 then ds.Item else null end) [redirect_numplan]--,
--max(case ds.ItemNumber
--when 44 then ds.Item else null end),
--max(case ds.ItemNumber
--when 45 then ds.Item else null end)
into
dbo.CDRStaging
from
SampleData sd
cross apply dbo.DelimitedSplit8K(SampleRec,'|') ds
group by
sd.SampleRec;
Viewing 15 posts - 46 through 60 (of 95 total)
You must be logged in to reply to this topic. Login to reply