June 29, 2012 at 6:36 pm
I would be carefully with triggers. If the write to dbo.CDR failed for some reason, the write of the record to dbo.Syslogd would rolled back as well.
The question I would ask, is how current does the data need to be in dbo.CDR? Does it really need to be updated on a near real time basis?
June 29, 2012 at 6:38 pm
rjbirkett (6/29/2012)
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?
Can you? Yes.
What's your timeout on the syslog delivery from the N-Tier? If it's too low you might end up with a few fallouts due to timeouts while it waits for one before it processes the next insert. That usually only happens during really busy times, which is when you can't afford the delay in the first place. That said, 2000 records? GO FOR IT! 😉 Just make sure your delivery has something like a 5 second timeout or greater in place in case of random silliness. Nothing extreme, just enough.
You'd usually go to a messaging app of some kind for nearly real time conversion, but you want to talk about your cans of worms...
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 6:53 pm
Ha! No fear. OK, maybe a little bit. I modified the select, sorted out my tables, did some messing around and found only one problem. It all dies when a blank field between the delimiters is encontered. Basically (as I stated earlier) CDR data stinks. I just looked through some 7000 exported records and found out that occasionally, the SECOND date time field with the UTC string in it that we are trying to split up has no data. I have no idea why, I wish I did, but the code Lynn put together produces an error that states The text, ntext and image datatypes cannot be compared or sorted, except when using IS NULL or LIKE operators. I ran the select query with the PATINDEX('%CALL_END%') clause and it returns 1900 records from yesterday (thanks Lynn), so the problem is with the blanks field I "think". I ran into this with my code but it is easy to fix when RBAR is used:-) I just inserted the first UTC string into the blank one and soldiered on..... So, my last request on this thread (I am now terribly afraid of triggers after your description) can we fix this? Try it with the test data I proveded, run the cript with a blank in the middle of the three fields.....
Robert.
PS. I am glad I joined this forum. I never expected to get this much help. I usually learn everything myself, the only way to really learn, but I was stumped. You have all done a great deal to restore my faith in the oline community. Newbies like me usually get hauled over the coals for asking what experts consider to be dumb questions.
June 29, 2012 at 6:59 pm
Can you provide us a copy/paste of the raw with the missing data that's causing the error? If we can see the error specifically and have the raw to work from we can puzzle it out.
Regarding the newbies, well, I won't say we're perfect but we do try our best to be friendly to everyone. We DO have our rabid dogs here, too, though... *cough* CELKO *cough*... so just ignore the obviously rediculous, don't worry, we'll (usually) get to ya. 🙂
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 7:01 pm
I will look into it. As for the SQL Server Community, you will find it unique among communities. We are willing to share our knowledge and experience freely.
June 29, 2012 at 7:07 pm
I will post it, but which tags to use? They are all code, this is delimited text strings.
In the interest of getting things done....
"|CALL_END |22 |0 |0 |23 |250 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |4147361715 |4147361715 |0 |0 |7260 |7260 |392 |g711Ulaw64k |20 |10.207.0.14 |7180 |IP |GWAPP_NORMAL_CALL_CLEAR |0 |9178 |19624 |36 |-1 |154130742216201210217@10.207.60.2 | 10:21:07.000 UTC Jun 21 2012 | 10:21:07.000 UTC Jun 21 2012 | 10:27:40.000 UTC Jun 21 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 10:27:40]"
"|CALL_END |65 |0 |2 |18 |0 |1 |ISDN |IP |10.207.15.114 |10.207.60.2 |0 |0 |0004F214E5E4 |0004F214E5E4 |0 |0 |7156234749 |7156234749 |0 |N/A |255 | |0 |TEL |GWAPP_FACILITY_REJECT |0 |2509 |2514 |5 |-1 |5ed5a021-4277fe77-c3c29dc8@10.207.15.114 | 11:59:04.000 UTC Jun 21 2012 | | 11:59:04.000 UTC Jun 21 2012 |214 |4 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 11:59:04]"
"|CALL_END |32 |0 |1 |9 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |7154821626 |7154821626 |0 |0 |7240 |7240 |5 |g711Ulaw64k |20 |10.207.0.10 |49366|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |266 |267 |0 |-1 |327693690216201211651@10.207.60.2 | 11:06:51.000 UTC Jun 21 2012 | 11:06:51.000 UTC Jun 21 2012 | 11:06:56.000 UTC Jun 21 2012 |224 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 11:06:56]"
June 29, 2012 at 7:11 pm
Does this have anything to do with the fact I'm using SQL 2008 and nchar is no longer supported, or am I just trying to be clever googling the error message?
Rob
June 29, 2012 at 7:20 pm
nchar, nvarchar, and nvarchar are supported, text and ntext have been depreciated.
June 29, 2012 at 7:32 pm
Is the second datetime the only one that can be blank (or null)?
June 29, 2012 at 7:42 pm
Lynn, out of the last 7 days data this is the only discrepency I have found. That second field is blank in 11 records out of 7000, but it just so happens that the first occurrence is in the first 25 records of the databse if I just run a select query with %CALL_END% as the criteria. 1987 records using your original query by MsgDate -1 so what else can it be? I don't know. In the error is says line 109 is the offending line.
USE [temp_cdrdb]
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
MsgText as SampleRec
from
[SYSLOG].[dbo].[Syslogd]
where
MsgDate = dateadd(dd, -1, cast(getdate() as date)) and PATINDEX('%CALL_END%', MsgText) > 0
)
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
Rob.
June 29, 2012 at 7:50 pm
Yep, as I thought. Take out the Group By statement at the end, you get a message related to time/date conversion and a NULL value being eiminated at some other SET operation, so I'm lamost (but not entirely) sure that is the issue. How to fix it, not sure at all. But we made a lot of progress, I am stoked, I learned a lot, and I can't fix Lynn's code:-( I'm done for the day, I need a beer. I will sleep on it and se if I can do a test on the second one of these to see if it is NULL. Time to watch Bill Maher on HBO:-)
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],
June 30, 2012 at 8:21 am
So, after another few hours of reading and just trying to get something to work, I still hit a brick wall. I'm sure that I am running the code correctly, but if you remember my question regarding how this code would cycle through the recorset, it seems I was questioning the process for good reason. Look at the code below. The select statement on its own returns nearly 2000 records for Friday. Add it to the code Lynn put together and take out the conversion statements (since they won't work when a blank field is encountered) and the whole query and code runs. BUT, you only get one record in the database. Why is that? what part of "cross apply" is processing 2000 records and writing just one to the CDRStaging table?
USE [tempCDRdb]
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
MsgText as SampleRec
from
[SYSLOG].[dbo].[Syslogd]
where
MsgDate = dateadd(dd, -2, cast(getdate() as date)) and PATINDEX('%CALL_END%', MsgText) > 0
)
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 ds.Item else null end) [callsetup_datetime],
--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 ds.Item else null end) [callconnect_datetime],
--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 ds.Item else null end) [callrelease_datetime],
--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
June 30, 2012 at 9:51 am
Well, I tried everything. The code does not loop through the records. It just produces 1 entry and finishess. Oddly enough ALL the examples I have looked at (including the document link you gave me and other CLR examples) all supply the variables/strings in their "test" procedures. Not one example uses a select statement to pull the variables out of a column and process the whole recordset. It all works brilliantly, as long as you supply a string for it to work on. Otherwise you get one record. So, although RBAR might be slow, at least it does work on a couple of thousand records. I will keep plugging away, but for now I might go back through this thread and see if Bill has a workable solution for a full set of extracted records.
Rob
June 30, 2012 at 10:00 am
Bill, you are doing the same thing in your code as everyone else appears to be doing. You supply the srting to process and "Voila!" it works. Yet it fails if I use a select statement to pull the data and process it. How about this: Take the sample data I posted, put it in a table/column, and select that data and see if you code will return a complete set of processed records. Yeah, I am a newbie, but I did read up on the split function and I now understand how it works if you supply a text string as a variable. But no one seems to have tried it using a select query to privide the input string. Neither solution returns more than 1 record.
Rob.
June 30, 2012 at 10:52 am
rjbirkett (6/30/2012)
So, after another few hours of reading and just trying to get something to work, I still hit a brick wall. I'm sure that I am running the code correctly, but if you remember my question regarding how this code would cycle through the recorset, it seems I was questioning the process for good reason. Look at the code below. The select statement on its own returns nearly 2000 records for Friday. Add it to the code Lynn put together and take out the conversion statements (since they won't work when a blank field is encountered) and the whole query and code runs. BUT, you only get one record in the database. Why is that? what part of "cross apply" is processing 2000 records and writing just one to the CDRStaging table?
USE [tempCDRdb]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
MsgText as SampleRec
from
[SYSLOG].[dbo].[Syslogd]
where
MsgDate = dateadd(dd, -2, cast(getdate() as date)) and PATINDEX('%CALL_END%', MsgText) > 0
)
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 ds.Item else null end) [callsetup_datetime],
--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 ds.Item else null end) [callconnect_datetime],
--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 ds.Item else null end) [callrelease_datetime],
--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
Still working on some changes, got tired last night and didn't finish it.
Viewing 15 posts - 61 through 75 (of 95 total)
You must be logged in to reply to this topic. Login to reply