Hi all,
I haven't encontered anything like this before, and as such am not sure how to best tackle it.
I have a table of phone calls coming into an phone system (Genesys) some of these calls are transfered between agents and queues there is a unique "Interaction_id" for each call and I can tell it's been transfered when it gets to another "login_name", unfortunely a call could potentially be transferred an infinite number of times which is where my issue is. My data looks like for a single interaction:
| CAL_DATE | INTERACTION_ID | QUEUE_NAME | LOGIN_NAME |
|---|
| 2015-07-09 15:00:26 | 108170179 | Business_Account_Unknown_vq | t963092 |
| 2015-07-09 15:02:27 | 108170179 | Business_MobileCalling_Resolve_Unknown_vq | t955202 |
| 2015-07-09 15:03:49 | 108170179 | Business_FixedLineData_Resolve_BBA_vq | t820093 |
| 2015-07-09 15:15:10 | 108170179 | Consumer_Unknown_Unknown_vq | t951129 |
What I need to do is show transfers in and transfers out - the first call wouldn't be a transfer in and the last wouldn't be a transfer out, so it would look like:
| CAL_DATE | INTERACTION_ID | QUEUE_NAME | LOGIN_NAME | TRANSFER_IN | TRANSFER_OUT |
|---|
| 2015-07-09 15:00:26 | 108170179 | Business_Account_Unknown_vq | t963092 | 0 | 1 |
| 2015-07-09 15:02:27 | 108170179 | Business_MobileCalling_Resolve_Unknown_vq | t955202 | 1 | 1 |
| 2015-07-09 15:03:49 | 108170179 | Business_FixedLineData_Resolve_BBA_vq | t820093 | 1 | 1 |
| 2015-07-09 15:15:10 | 108170179 | Consumer_Unknown_Unknown_vq | t951129 | 1 | 0 |
So I think what I need to do is perhaps something like CASE WHEN xxx LEAD xxx OVER (PARTITION BY INTERACTION_ID ORDER BY CAL_DATE DESC)?
Any help appreciated.
Thanks,
Thomas