Extract data based on data in multiple columns and rows

Question

I have the following data, which is a call flow coming to our center and transmitted to a consultant (CSO).

CallID Sequence Action Location Input NextLocation 1135 0 CallStart NULL NULL NULL 1135 1 MenuStart EFTPosHelpDesk NULL NULL 1135 2 KeyPress EFTPosHelpDesk 3 TransferCSO 1135 3 TransferEntry EFTPosHelpDesk NULL NULL 1135 4 TransferFlag NULL NULL NULL 1135 5 AccessNum NULL NULL NULL 1135 6 Transfer NULL NULL NULL 1135 7 Hangup NULL NULL NULL 

I use MS SQL Management Studio 2005 to retrieve the data, however the data itself is stored on the MS SQL 2000 server.

Literally millions of calls are recorded in the database, and I need to extract the CallID, where the client was transferred to CSO. The target data that tells me about the call was transmitted by the CSO exactly as follows:

 @Sequence 2 -> NextLocation = 'TansferCSO' @Sequence 3 -> Action = 'TransferEntry' @Sequence 4 -> Action = 'TransferFlag' @Sequence 5 -> Action = 'AccessNum' @Sequence 6 -> Action = 'Transfer' @Sequence 7 -> Action = 'Hangup' 

... and always in this sequence, but the numbering "Sequence" and "Location" will be different, because some calls can be performed on average 50 - 70 steps, and we have 100 IVR (locations).

I am new to SQL and I have tried using FETCH and IF / ELSE, but to no avail. ROW_NUMBER () does not work due to data residing on MS SQL2000 server.

Any examples or recommendations are appreciated.

Answer 1

This will give you all the calls (CallID) that were transferred to the CSO based on all 6 present entries (in any order).

  select CallID from yourTable where Action in ('TransferEntry', 'TransferFlag', 'AccessNum', 'Transfer', 'Hangup') or NextLocation = 'TransferCSO' group by CallID having count(distinct Action) = 6; 

Note. It is assumed that a line with NextLocation='TransferCSO' has a different but specific action, for example. "KeyPress"

---

If you absolutely need to ensure that 6 events are consistent, you can use below. A small change in the JOIN conditions at the end can also be used for sequence, not necessarily necessarily sequential.

 create table #tmpCalls (CallID int, Sequence int, Action varchar(20)); select c.CallID, c.Sequence, c.Action from ( select CallID from yourTable where Action in ('TransferEntry', 'TransferFlag', 'AccessNum', 'Transfer', 'Hangup') or (Action = 'KeyPress' and NextLocation = 'TransferCSO') group by CallID having count(distinct Action) = 6 ) a join yourTable c on c.CallID = a.CallID where c.Action in ('TransferEntry', 'TransferFlag', 'AccessNum', 'Transfer', 'Hangup') or (c.Action = 'KeyPress' and c.NextLocation = 'TransferCSO'); create clustered index #ix_tmpCalls on #tmpCalls(CallID, Sequence, Action); select distinct a.CallID from #tmpCalls a -- or perhaps just: b.Sequence > a.Sequence join #tmpCalls b on b.Action = 'TransferEntry' and b.Sequence = a.Sequence + 1 join #tmpCalls c on c.Action = 'TransferFlag' and c.Sequence = b.Sequence + 1 join #tmpCalls d on d.Action = 'AccessNum' and d.Sequence = c.Sequence + 1 join #tmpCalls e on e.Action = 'Transfer' and e.Sequence = d.Sequence + 1 join #tmpCalls f on f.Action = 'Hangup' and f.Sequence = e.Sequence + 1 where a.Action = 'KeyPress' and a.NextLocation = 'TransferCSO'; 

Note that the subquery is the original query to narrow down the candidates. A temporary table is used to make it work quickly, since we can copy it around three columns.