Problem in Calculating the Date differnce based on 2 Status which are repeating multiple times

[aveeeeeee7en]

TICKETNUMBER	WORKSTATUS	MODIFIEDON
CD1300000256246	Pending for Customer Approval	2/6/2013
CD1300000256246	Customer Approved	2/7/2013
CD1300000256246	Pending for Customer Approval	2/8/2013
CD1300000256246	Customer Approved	2/8/2013
CD1300000256246	Pending for Customer Approval	2/8/2013
CD1300000256246	Customer Approved	2/8/2013
CD1300000256246	Pending for Customer Approval	2/9/2013
CD1300000256246	Customer Approved	2/9/2013
CD1300000256246	Pending for Customer Approval	2/9/2013
CD1300000256246	Customer Approved	2/13/2013

There is 1 unique TickerNumber against which there are several status.

What i need is to find out the difference between 1st 'Pending for Customer Approval' status and

2nd 'Customer Approved' status than again find out the differnce between 3rd 'Pending for Customer Approval' status

and 4th 'Customer Approved' status and so on and at last i want  to sum all the differences.

The desired result should be like this :

TICKETNUMBER	WORKSTATUS	MODIFIEDON	Customer Approved - Pending for Customer Approval	Sum
CD1300000256246	Pending for Customer Approval	2/6/2013	2/7/2013 - 2/6/2013	1
CD1300000256246	Customer Approved	2/7/2013
CD1300000256246	Pending for Customer Approval	2/8/2013	2/8/2013 - 2/8/2013	0
CD1300000256246	Customer Approved	2/8/2013
CD1300000256246	Pending for Customer Approval	2/8/2013	2/8/2013 - 2/8/2013	0
CD1300000256246	Customer Approved	2/8/2013
CD1300000256246	Pending for Customer Approval	2/9/2013	2/9/2013 - 2/9/2013	0
CD1300000256246	Customer Approved	2/9/2013
CD1300000256246	Pending for Customer Approval	2/9/2013	2/13/2013 - 2/9/2013	4
CD1300000256246	Customer Approved	2/13/2013
			TOTAL	5

[spividori · Accepted solution]

Hi.

See the attached example.

Hope this help!.

Regards.

[aveeeeeee7en]

Thank You very much Sir spividori

This is what exactly i need.

[aveeeeeee7en]

Sir your Answer was very helpful but the data i have shared earlier was just of 1 case there were around thousands of cases.

So, i cant assign 1 or 2 for Status using Inline function since i don't know the order of Status. That is not possible for below data. I am sharing you another scenario. Please help me on this.

TICKETNUMBER	WORKSTATUS	MODIFIEDON
CD1300000304725	Pending for Customer Approval	10-07-13
CD1300000304725	Customer Approved	11-07-13
CD1300000304725	Customer Approved	11-07-13
CD1300000304725	Pending for Customer Approval	11-07-13
CD1300000304725	Pending for Customer Approval	11-07-13
CD1300000304725	Pending for Customer Approval	24-07-13
CD1300000304725	Pending for Customer Approval	26-07-13
CD1300000304725	Customer Approved	12-08-13
CD1300000304725	Pending for Customer Approval	13-08-13
CD1300000304725	Customer Approved	13-08-13

What exactly i need is :

TICKETNUMBER	WORKSTATUS	MODIFIEDON	Cases to Pick	Sum (Total)
CD1300000304725	Pending for Customer Approval	10-07-13	Min1	Max1-Min1 = 1 Day
CD1300000304725	Customer Approved	11-07-13
CD1300000304725	Customer Approved	11-07-13	Max1
CD1300000304725	Pending for Customer Approval	11-07-13	Min2	Max2-Min2 = 30 Days
CD1300000304725	Pending for Customer Approval	11-07-13
CD1300000304725	Pending for Customer Approval	24-07-13
CD1300000304725	Pending for Customer Approval	26-07-13
CD1300000304725	Customer Approved	12-08-13	Max2
CD1300000304725	Pending for Customer Approval	13-08-13	Min3	Max3 - Min3 = 0 Day
CD1300000304725	Customer Approved	13-08-13	Max3
			TOTAL	1 + 30 + 0 = 31 Days
	To Pick Minimum Ticket Number and Maximum Creation date not only once but number of times if Multiple status appear against same Ticket Number.

[spividori]

Hi Nag.

See the attached example.

Hope this help!.

Regards.