asked on

qry help

I have a table as follows:

LOGID status created_dt
1 begin 07/26/2012 1:16:26
2 talk 07/26/2012 1:17:26
3 hold 07/26/2012 1:18:26
4 end 07/26/2012 1:18:50
5 begin 07/26/2012 1:20:50
6 talk 07/26/2012 1:22:50
7 end 07/26/2012 1:23:50
8 begin 07/26/2012 1:25:50
9 begin 07/26/2012 1:26:50
10 talk 07/26/2012 1:27:50
11 hold 07/26/2012 1:28:20
12 talk 07/26/2012 1:30:20
14 end 07/26/2012 1:30:50

begin to end is considered as 1 call. Basically I want identify the calls and put a number next to it. Every call should have start and end. Some times there could be just a begin without any other status and I should discard basically mark it 0(eg number 8 where there is a begin but no end).

So i want the result as follows:

LOGID status created_dt cnt
1 begin 07/26/2012 1:16:26 1
2 talk 07/26/2012 1:17:26 1
3 hold 07/26/2012 1:18:26 1
4 end 07/26/2012 1:18:50 1
5 begin 07/26/2012 1:20:50 2
6 talk 07/26/2012 1:22:50 2
7 end 07/26/2012 1:23:50 2
8 begin 07/26/2012 1:25:50 0
9 begin 07/26/2012 1:26:50 3
10 talk 07/26/2012 1:27:50 3
11 hold 07/26/2012 1:28:20 3
12 talk 07/26/2012 1:30:20 3
14 end 07/26/2012 1:30:50 3

Please let know how to do this..

Thanks

SOLUTION

flow01

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gs79

ASKER

@Flow..

Thank you for your response. It works perfectly well for the above requirement.

Unfortunately I missed one more piece and now I am struggling a bit to incorporate this.

At times there is junk after 'end' before next 'begin'. like below. There can be 'end' after an 'end' without a begin which i need to exclude..

9 begin 07/26/2012 1:26:50 3
10 talk 07/26/2012 1:27:50 3
11 hold 07/26/2012 1:28:20 3
12 talk 07/26/2012 1:30:20 3
14 end 07/26/2012 1:30:50 3
15 hold 07/26/2012 1:30:50 0
16 end 07/26/2012 1:30:50 0
17 end 07/26/2012 1:30:50 0
18 begin 07/26/2012 1:30:50 4
..
..
21 end 07/26/2012 1:30:50 4

from the above example i want to ignore 15,16,17..

Please let me know how to incorporate this..

THanks

ps right now it is printing as follows:

9 begin 07/26/2012 1:26:50 3
10 talk 07/26/2012 1:27:50 3
11 hold 07/26/2012 1:28:20 3
12 talk 07/26/2012 1:30:20 3
14 end 07/26/2012 1:30:50 3
15 hold 07/26/2012 1:30:50 3
16 end 07/26/2012 1:30:50 3
17 end 07/26/2012 1:30:50 3
18 begin 07/26/2012 1:30:50 4
..
..
21 end 07/26/2012 1:30:50 4

ASKER CERTIFIED SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

namethis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gs79

ASKER

G!et answers..anlytic functions are cool!

I ended up implementing lwadwell solution..

Thanks