Querying values from different records to show as one record? 1

[scottyjohn]

Hi all,
I have the following Data....

ID START(UTC) END(UTC) DURATION STATE TRANSACTION_ID
------------------------------------------------------
1 1251384001 1251384102 101 INBOUND 31808563970
2 1251384102 1251384202 100 HOLD 31808563970
3 1251384202 1251384302 100 INBOUND 31808563970
4 1251384303 1251384402 99 INBOUND 31808563971
5 1251384402 1251384604 202 HOLD 31808563971
6 1251384604 1251384809 205 INBOUND 31808563971

Each transaction_ID is a unique voice call, and the example shows where the agent takes the call, then puts the customer on hold, then takes the call back again. What I need to be able to do is to run a query which will show a record containing the START of the first INBOUND status, the END time of the last INBOUND status for each Transaction_ID and a sum of the total inbound status time for that transaction_id. Would this be possible? I cant think how to achieve it.

Any help would be much appreciated

John

 

[carp]

How about something like

SELECT s.transaction_id, 
       MIN(s.start) AS start_utc, 
       MAX(e.end) AS end_utc,
       MAX(e.end) - MIN(s.start) AS DURATION
FROM my_table s, my_table e
WHERE s.transaction_id = e.transaction_id
GROUP BY s.transaction_id;

I haven't tested this, it's early and the coffee isn't fully engaged yet, but it seems like this should be fairly close.

 

[taupirho]

SELECT transaction_id
MIN(start) AS start_utc,
MAX(end) AS end_utc,
SUM(duration) DURATION
FROM your_table
WHERE state = 'INBOUND'
GROUP BY transaction_id;


In order to understand recursion, you must first understand recursion.

 

[carp]

Yep -
Now that I am fully awake and reread your OP, I thin taupirho has it. Now, if you also only want transactions with more than one record, you might want to add
'HAVING count(*) > 1' to T's query.

 

[scottyjohn]

Ha ha! Yes I think taupirho's solution does do what I need. I was also wondering if there was a way to do the same for the HOLD status on the same result line of a query? But for this, I would only need a total duration, not the start and end times as above? Something like this....

STARTTIME|ENDTIME|TRANS_ID|INBOUND_DURATION|HOLD_DURATION

I have tried a few variations on your query above but I cant get it quite right.....

Thanks for your help!

John

 

[taupirho]

Scotty, you sound as though your up for thinking for your self which is refreshing. So rather than giving the answer straight out, a couple of clues.

Think about using the case statement with your aggregrates and modifying your group by clause

That should give you what you need but you will have 2 records per ID, one for HOLD and one for INBOUND. You need to flatten these down into one record so an additional use of MAX and decode with an inline view should sort this out for you.


In order to understand recursion, you must first understand recursion.

 

[scottyjohn]

Thanks Taupirho,
Im struggling to apply the case and decode functions as Im not really familiar with them and googling shows me examples of what they do, Im just not sure how to apply them to my problem?
Ill keep plugging and try to work it out but if you have any other guidance it would be a great help.

John

 

[taupirho]

SQL> select * from tom;

ID START_UTC END_UTC DURATION STATE TRANSACTION_ID
---------- ---------- ---------- ---------- ---------- ------------------
1 1251384001 1251384102 101 INBOUND 31808563970
2 1251384102 1251384202 100 HOLD 31808563970
3 1251384202 1251384302 100 INBOUND 31808563970
4 1251384303 1251384402 99 INBOUND 31808563971
5 1251384402 1251384604 202 HOLD 31808563971
6 1251384604 1251384809 205 INBOUND 31808563971

6 rows selected.

1 select transaction_id,
2 max(decode(state,'INBOUND',start_utc)) start_utc,
3 max(decode(state,'INBOUND',end_utc)) end_utc,
4 max(decode(state,'INBOUND',duration)) inbound_duration,
5 max(decode(state,'HOLD',duration)) hold_duration
6 from
7 (
8 SELECT transaction_id,
9 state,
10 case
11 when state = 'INBOUND' then MIN(start_utc) end start_utc,
12 case
13 when state = 'INBOUND' then MAX(end_utc) end end_utc,
14 case
15 when state = 'INBOUND' then SUM(duration)
16 when state = 'HOLD' then SUM(duration)
17 end DURATION
18 FROM tom
19 GROUP BY transaction_id,state
20 )
21* group by transaction_id
SQL> /

TRANSACTION_ID START_UTC END_UTC INBOUND_DURATION HOLD_DURATION
------------------ ---------- ---------- ---------------- -------------
31808563970 1251384001 1251384302 201 100
31808563971 1251384303 1251384809 304 202

SQL>



In order to understand recursion, you must first understand recursion.

 

[scottyjohn]

Ahh fantastic, I would never have worked that out myself, at least not this year!
Thanks, Ill now go away and research how this does what it does and hopefully not have to pester you pros again!

Many thanks

John