Good morning, I'm not sure if this is possible but I thought I'd ask you guys. I have a column in a table that is called "CallEventLog", in this column it holds log information about a call. In the example below I would like to perform a date diff on the Initializing time & the time it enters the Workgroup. Is this possible if all the data is combined into one column?
Ifor understood it differently i understood it differently
anyhow
create sample data script
CREATE TABLE #t
(
CallEventLog varchar(50)
);
INSERT INTO #t select '09:38:34: Initializing'
INSERT INTO #t select '09:38:34: Offering'
INSERT INTO #t select '09:38:36: ANI: 07920000000'
INSERT INTO #t select '09:38:37: DNIS: +4417378888888'
INSERT INTO #t select '09:38:37: Call answered'
INSERT INTO #t select '09:38:44: Entered Workgroup'
INSERT INTO #t select '09:38:45: Default Schedule'
INSERT INTO #t select '09:38:47: Closed Menu'
INSERT INTO #t select '09:38:51: Disconnected [Remote Disconnect]'
INSERT INTO #t select '10:38:34: Initializing'
INSERT INTO #t select '10:38:34: Offering'
INSERT INTO #t select '10:38:36: ANI: 07920000000'
INSERT INTO #t select '10:38:37: DNIS: +4417378888888'
INSERT INTO #t select '10:38:37: Call answered'
INSERT INTO #t select '10:38:42: Entered Workgroup'
INSERT INTO #t select '10:38:45: Default Schedule'
INSERT INTO #t select '10:38:47: Closed Menu'
INSERT INTO #t select '10:38:51: Disconnected [Remote Disconnect]'
select * from #t
; with cte_init as
(
select
row_number() over( order by cast(left(calleventlog,charindex(': ',calleventlog)-1) as time ) ) as rn
, cast(left(calleventlog,charindex(': ',calleventlog)-1) as time) as time_init
, calleventlog
from
#t
where
CallEventLog like '%Initializing%'
)
,cte_enter as
(
select
row_number() over( order by cast(left(calleventlog,charindex(': ',calleventlog)-1) as time ) ) as rn
, cast(left(calleventlog,charindex(': ',calleventlog)-1) as time) as time_enter
, calleventlog
from
#t
where
CallEventLog like '%Entered Workgroup%'
)
select
datediff(ss,a.time_init,b.time_enter)
, *
from
cte_init a
join
cte_enter b
on a.rn = b.rn
Yes, this is what I'm looking for, but the calculation in the query results returns zero. See below what I'm running and the logs themselves ;
; with cte_init as
(
select
row_number() over( order by cast(left(calleventlog,charindex(': ',calleventlog)-1) as time ) ) as rn
, cast(left(calleventlog,charindex(': ',calleventlog)-1) as time) as time_init
, calleventlog
from [dbo].[calldetail_viw]
where
CallEventLog like '%Initializing%' and callid in ('300138599530230112', '200162117920230112')
)
,cte_enter as
(
select
row_number() over( order by cast(left(calleventlog,charindex(': ',calleventlog)-1) as time ) ) as rn
, cast(left(calleventlog,charindex(': ',calleventlog)-1) as time) as time_enter
, calleventlog
from [dbo].[calldetail_viw]
where
CallEventLog like '%GBR Call In%' and callid in ('300138599530230112', '200162117920230112')
)
select
datediff(ss,a.time_init,b.time_enter)
, *
from
cte_init a
join
cte_enter b
on a.rn = b.rn
09:38:34: Initializing
09:38:34: Offering
09:38:36: ANI: 07920000000
09:38:37: DNIS: +441737999999
09:38:37: Call answered
09:38:44: GBR Call IN - PI Collections
09:38:45: Default Schedule
09:38:47: Closed Menu
09:38:51: Disconnected [Remote Disconnect]
and
10:07:40: Initializing
10:07:40: Offering
10:07:40: ANI: 07920000000
10:07:40: DNIS: +441737288888
10:07:40: Call answered
10:07:40: GBR Call IN - PI Collections
10:07:40: Open
10:07:41: Menu
10:07:43: Disconnected [Remote Disconnect]
Hi yes the data looks like that, the only difference is where I have added "Entered Workgroup" the actual data says "GBR Call In - PI Collections"
This is an actual log with the telephone numbers amended
09:38:34: Initializing
09:38:34: Offering
09:38:36: ANI: 07920000000
09:38:37: DNIS: +441737888888
09:38:37: Call answered
09:38:44: GBR Call IN - PI Collections
09:38:45: Default Schedule
09:38:47: Closed Menu
09:38:51: Disconnected [Remote Disconnect]
I did try IFORS code and got it working if I manually entered the calleventlog information, but I want something that I can just link to the table where the calleventlog is. In this case the table is "[dbo].[calldetail_viw]" and I want to calculate the difference between the "Initializing" time and the "GBR Call IN - PI Collections time" from the calleventlog.
; with cte_init as
(
select
row_number() over( order by cast(left(calleventlog,charindex(': ',calleventlog)-1) as time ) ) as rn
, cast(left(calleventlog,charindex(': ',calleventlog)-1) as time) as time_init
, calleventlog
from [dbo].[calldetail_viw]
where
CallEventLog like '%Initializing%' and callid in ('300138599530230112', '200162117920230112')
)
,cte_enter as
(
select
row_number() over( order by cast(left(calleventlog,charindex(': ',calleventlog)-1) as time ) ) as rn
, cast(left(calleventlog,charindex(': ',calleventlog)-1) as time) as time_enter
, calleventlog
from [dbo].[calldetail_viw]
where
CallEventLog like '%GBR Call IN%' and callid in ('300138599530230112', '200162117920230112')
)
select
datediff(ss,a.time_init,b.time_enter)
, *
from
cte_init a
join
cte_enter b
on a.rn = b.rn
@davidmatthewjames
This looks like it's from a Genesis call system. If that's correct, then be advised that this column has a max limit and WILL truncate, sometimes before the data you actually want. Genesis has flat refused to change it to LOB and that means the column is an unreliable source of data. I strongly recommend you find another source table for the data. I have no clue what table that might be because we also have a 3rd party programming vendor and I was told they'd take care of the issue (to date, they haven't but them blowing smoke is a different issue).
