5 Replies Latest reply: Sep 7, 2013 5:48 PM by AVIRAL NAG

# Calculate Date Difference for Various Status against 1 Number

Need help on this :

I have the data against this Ticket Number :

 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 :

 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
• ###### Re: Calculate Date Difference for Various Status against 1 Number

See attached qvw

• ###### Re: Calculate Date Difference for Various Status against 1 Number

Thank You very much

• ###### Re: Calculate Date Difference for Various Status against 1 Number

There are some lag in the logic.

 ROW TICKETNUMBER WORKSTATUS MODIFIEDON REFDATE 39457 CD1300000292734 Pending for Customer Approval 29/05/13 29/05/13 39450 CD1300000292734 Pending for Customer Approval 30/05/13 29/05/13 40138 CD1300000292734 Pending for Customer Approval 03/06/13 29/05/13 4630 CD1300000292734 Customer Approved 05/06/13 29/05/13 373 CD1300000292734 Pending for Customer Approval 17/06/13 17/06/13 2603 CD1300000292734 Pending for Customer Approval 17/06/13 17/06/13 5373 CD1300000292734 Pending for Customer Approval 19/06/13 17/06/13 6326 CD1300000292734 Pending for Customer Approval 22/06/13 17/06/13 7144 CD1300000292734 Pending for Customer Approval 26/06/13 17/06/13 6948 CD1300000292734 Pending for Customer Approval 01/07/13 17/06/13 3528 CD1300000292734 Pending for Customer Approval 04/07/13 17/06/13 7666 CD1300000292734 Pending for Customer Approval 09/07/13 17/06/13

For above case it should calculate only the Highlighted data but it is Calculating others also.

Since i need both Pending for Customer Approval and Customer Approval if they both are coming so pick max and min dates.

Highlighted data is correct since it has both Pending for customer approval and Customer Approved Dates but below after this there are Pending for Customer Approval multiple cases but there is no Customer Approval Status. So. we dont want to need these dates. But According to you Calculation it is calculating its difference also.

 TICKETNUMBER max(MODIFIEDON)-only(REFDATE) 28.74 CD1300000292734 6.81 CD1300000292734 21.93

Here the data should be 6.81 only. 21.93 not to be count.

• ###### Re: Re: Calculate Date Difference for Various Status against 1 Number

You can try with a reversed order of your rows, so you only set a refdate when there is Approval.

I used ROW for reverse sorting your records, but maybe you are using a timestamp with your real data, which would be more appropriate that a plain date value for multiple status entries.

• ###### Re: Calculate Date Difference for Various Status against 1 Number
 TICKETNUMBER WORKSTATUS MODIFIEDON 11.85318 CD1300000292734 Pending for Customer Approval 20/05/13 Pick Minimum Date 20/05/13 Maximum Date - Minimum Date CD1300000292734 Pending for Customer Approval 21/05/13 CD1300000292734 Pending for Customer Approval 23/06/13 CD1300000292734 Customer Approved 01/06/13 Pick Maximum Date 01/06/13 CD1300000292734 Pending for Customer Approval 29/05/13 Pick Minimum Date1 29/05/13 Maximum Date1 - Minimum Date1 6.8136 CD1300000292734 Pending for Customer Approval 30/05/13 CD1300000292734 Pending for Customer Approval 03/06/13 CD1300000292734 Customer Approved 03/06/13 Pick Maximum Date1 CD1300000292734 Customer Approved 05/06/13 05/06/13 CD1300000292734 Pending for Customer Approval 17/06/13 Not to be used since after 'Pending for Customer Approval' there is no 'Customer Approval'. So, I don’t have to consider these cases. CD1300000292734 Pending for Customer Approval 17/06/13 CD1300000292734 Pending for Customer Approval 19/06/13 CD1300000292734 Pending for Customer Approval 22/06/13 CD1300000292734 Pending for Customer Approval 26/06/13 CD1300000292734 Pending for Customer Approval 01/07/13 CD1300000292734 Pending for Customer Approval 04/07/13 CD1300000292734 Pending for Customer Approval 09/07/13 Total 18.6667

Need help on the above example.