sql server programming: Hello:

Currently I have a large SQL Server 2000 DTS package that does many
things--I was able to do everything except one without using cursors. I
want to get rid of the one instance I use the cursor however as it
holds things up.

This is a database that measures events in the life of a phone call. I
have two tables, one with one line representing the entire call,
another with multiple records for the call, representing times a
customer was put on hold and then released from hold. These hold events
are ordered only by time to the second, so might be out of order if two
events happened on the same second. My goal is to capture both the
number of times a call was put on hold, and the total time the call
spent on hold. Here is some sample data:

CallTable
TelephoneCallID CallAnsweredTime NumberTimesOnHold
TimeOnHold
1 9:00:53 3 (need
to calculate from below) 00:03:10 (from below)

HoldEventsTable
TelephoneCallID Time Event
1 9:01:12 CallPutOnHold
1 9:01:20 CallReleasedFromHold
1 9:03:13 CallPutOnHold
1 9:05:13 CallPutOnHold
1 9:05:13 CallReleasedFromHold
1 9:06:15 CallReleasedFromHold
1 9:07:16 CallHangsUp

Also, the call might be disconnected while it is on hold, so in that
case I would need the time difference between the time the call went on
hold and the time.

How can I do this besides line by line cursor analysis?

Thanks!
Kayda

Hello, Kayda

Assuming the following DDL and sample data:

CREATE TABLE HoldEventsTable (
TelephoneCallID int NOT NULL,
Time datetime NOT NULL,
Event varchar(20) NOT NULL,
PRIMARY KEY (Time, TelephoneCallID, Event)
)

INSERT INTO HoldEventsTable VALUES (1, '20070115 9:01:12',
'CallPutOnHold')
INSERT INTO HoldEventsTable VALUES (1, '20070115 9:01:20',
'CallReleasedFromHold')
INSERT INTO HoldEventsTable VALUES (1, '20070115 9:03:13',
'CallPutOnHold')
INSERT INTO HoldEventsTable VALUES (1, '20070115 9:05:13',
'CallPutOnHold')
INSERT INTO HoldEventsTable VALUES (1, '20070115 9:05:13',
'CallReleasedFromHold')
INSERT INTO HoldEventsTable VALUES (1, '20070115 9:06:15',
'CallReleasedFromHold')
INSERT INTO HoldEventsTable VALUES (1, '20070115 9:07:16',
'CallHangsUp')

INSERT INTO HoldEventsTable VALUES (2, '20070115 9:02:12',
'CallPutOnHold')
INSERT INTO HoldEventsTable VALUES (2, '20070115 9:02:20',
'CallReleasedFromHold')
INSERT INTO HoldEventsTable VALUES (2, '20070115 9:03:13',
'CallPutOnHold')
INSERT INTO HoldEventsTable VALUES (2, '20070115 9:05:16',
'CallHangsUp')

INSERT INTO HoldEventsTable VALUES (3, '20070115 23:59:30',
'CallPutOnHold')
INSERT INTO HoldEventsTable VALUES (3, '20070116 00:01:20',
'CallReleasedFromHold')

You can get the expected result using this query:

SELECT TelephoneCallID, COUNT(*) AS NumberTimesOnHold,
CONVERT(varchar(8),DATEADD(s,
SUM(DATEDIFF(s, HoldStartTime, HoldEndTime))
,0),108) as TimeOnHold
FROM (
SELECT TelephoneCallID, Time as HoldStartTime, (
SELECT MIN(Time) FROM HoldEventsTable r
WHERE r.TelephoneCallID=h.TelephoneCallID
AND r.Time>h.Time
AND Event IN ('CallReleasedFromHold','CallHangsUp')
) as HoldEndTime
FROM HoldEventsTable h
WHERE Event='CallPutOnHold'
) X
GROUP BY TelephoneCallID

Razvan

[quoted text, click to view]

Razvan,

Thanks for the test data!
In 2005 it is somewhat shorter:

SELECT b.TelephoneCallID, b.[time] HoldBegan, COALESCE(e.[time],
(SELECT MAX([Time]) FROM HoldEventsTable h WHERE h.TelephoneCallID =
b.TelephoneCallID)) HoldEnded
FROM (
SELECT TelephoneCallID, [time], ROW_NUMBER() OVER(PARTITION BY
TelephoneCallID ORDER BY [time]) rn
FROM HoldEventsTable WHERE [Event] = 'CallPutOnHold'
) b LEFT JOIN (
SELECT TelephoneCallID, [time], ROW_NUMBER() OVER(PARTITION BY
TelephoneCallID ORDER BY [time]) rn
FROM HoldEventsTable WHERE [Event] IN ('CallReleasedFromHold',
'CallHangsUp')
) e ON b.TelephoneCallID = e.TelephoneCallID AND b.rn = e.rn


-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

I though this one was interesting so I decided to see what I could
come up with. I used Razvan's table and test data. I did not bother
to convert the number of elapsed seconds to the time format.

My major concern was the cases when hold could start/end in the same
second. The approach I used was to assign a sequence number to each
type of event, then join the two types by that ordinal.

I also added more test data, including:

INSERT INTO HoldEventsTable VALUES (7, '20070116 8:00:10',
'CallPutOnHold')
INSERT INTO HoldEventsTable VALUES (7, '20070116 8:00:10',
'CallReleasedFromHold')
INSERT INTO HoldEventsTable VALUES (7, '20070116 8:02:25',
'CallPutOnHold')
INSERT INTO HoldEventsTable VALUES (7, '20070116 8:03:25',
'CallHangsUp')

The query below handles this case and returns an elapsed seconds of
60. The other solution returnd 00:04:15.

SELECT Starting.TelephoneCallID,
SUM(datediff(second, Starting.Time, Ending.Time)) as
ElapsedSeconds,
count(*) as TimesOnHold
FROM (SELECT TelephoneCallID,
Time,
(select count(*) from HoldEventsTable as B
where B.Event = 'CallPutOnHold'
and A.TelephoneCallID = B.TelephoneCallID
and A.Time >= B.Time) as Ordinal
FROM HoldEventsTable as A
WHERE Event = 'CallPutOnHold') as Starting
JOIN (SELECT TelephoneCallID,
Time,
(select count(*) from HoldEventsTable as D
where D.Event IN ('CallReleasedFromHold',
'CallHangsUp')
and C.TelephoneCallID = D.TelephoneCallID
and C.Time >= D.Time) as Ordinal
FROM HoldEventsTable as C
WHERE Event IN ('CallReleasedFromHold', 'CallHangsUp')) as
Ending
ON Starting.TelephoneCallID = Ending.TelephoneCallID
AND Starting.Ordinal = Ending.Ordinal
GROUP BY Starting.TelephoneCallID

Roy Harvey
Beacon Falls, CT

[quoted text, click to view]