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

    Calculate Date Difference for Various Status against 1 Number

    AVIRAL NAG

      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
            AVIRAL NAG

            Thank You very much

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

              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)

               

              1. 28.74

              CD1300000292734

              1. 6.81

              CD1300000292734

              1. 21.93

               

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

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

                TICKETNUMBER

                WORKSTATUS

                MODIFIEDON

                 

                 

                 

                1. 11.85318
                CD1300000292734Pending for Customer Approval20/05/13Pick Minimum Date20/05/13Maximum Date - Minimum Date
                CD1300000292734Pending for Customer Approval21/05/13
                CD1300000292734Pending for Customer Approval23/06/13
                CD1300000292734Customer Approved01/06/13Pick Maximum Date01/06/13
                CD1300000292734Pending for Customer Approval29/05/13Pick Minimum Date129/05/13Maximum Date1 - Minimum Date1
                1. 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

                1. 18.6667

                 

                 

                Need help on the above example.