+ Start a Discussion
sfdc@isha.ax1814sfdc@isha.ax1814 

help me on the trigger how to find diference between two dates?

Hi 
help me out on yhis requirement little urgency.i got one requirement that on case obect hat we have to caliculate the time difference between when the status is changed from  "working"  to status="closed" not the immediate change like after working it can go to any other change also then closed.i have written trigger but it is not working properly.can anybosy look in to this and help me out .

Public Class DateTimeDifferenceTriggerHelperClass
{
     public static Datetime Date1;
    public static Datetime Date2;                             
    Public static String DateDiff;                        
    Public static boolean ETMcheck = false; 


    Public static boolean Closecheck = false;
   public static  string DateTimeDiffCalcMethod(Map<Id,Case> caseId)
    {
            
   
    list<CaseHistory> casHistList = new list<CaseHistory>();
        casHistList = [SELECT CaseId,NewValue,CreatedDate,case.testtextfield__c FROM CaseHistory WHERE CaseId =:caseId.KeySet() ORDER BY CreatedDate ASC NULLS LAST];
  
  
    if(casHistList.size() > 0)
    {
   // datetime Date1;
    //datetime Date2;
        for(CaseHistory ch : casHistList)
        {
            if(ch.NewValue == 'Working' && ETMcheck == false)
            {
                Date1 = ch.CreatedDate;
                ETMcheck = true;
            }
            if(ch.NewValue == 'closed' && Closecheck == false)
            {
                  Date2 = ch.CreatedDate;
                Closecheck = true;
            }
        }
    }
  
    if(Date1 != null && Date2 != null)
    {
 
  
        integer Days = math.round(math.floor(Double.valueOf((Date2.getTime() - Date1 .getTime())/(1000*60*60*24))));  
        integer Hours = math.mod(integer.valueOf((Date2 .getTime() - Date1 .getTime())/(1000*60*60)),24);                          
        integer Minutes = math.mod(Integer.valueOf((Date2 .getTime() - Date1 .getTime())/(1000*60)),60);                            
      
        system.debug(Days +'***Days*** ' + '***Hours*** ' + Hours + '***Minutes*** ' + Minutes);
      
        DateDiff = Days + ' Days  ' + Hours + ' Hours  ' + Minutes + ' Minutes';

}
    
      system.debug('DateDiff@@@'+DateDiff);
      return DateDiff ;
        }
      
        }

Trigger:

trigger DateTimeDifferenceTriggerHelperClasstrigger on Case (before update) {

  If(trigger.isbefore && trigger.isUpdate)
    {
trigger.new[0].difffield__c =DateTimeDifferenceTriggerHelperClass.DateTimeDiffCalcMethod(trigger.newmap);
  }                     
}

difffield__c field is not populating properly..


regards,
isha

Best Answer chosen by sfdc@isha.ax1814
vmanumachu1.393650924860069E12vmanumachu1.393650924860069E12
Updated the code to include minutes and hours

trigger trgCalculateWorkingTime on Case (after update) {
List<Case> lstCase = new List<Case>();
List<Case> caseswithhistory  = [select Status,Id, (select c.OldValue, c.NewValue, c.Id, c.Field, c.CreatedDate, c.CaseId from Histories c where c.Field = 'Status') from Case where Id IN: Trigger.newMap.keyset()];
Map<Id,Case> mapOldcase = Trigger.oldMap;
//List<CaseHistory> cseHistory = [Select c.OldValue, c.NewValue, c.Id, c.Field, c.CreatedDate, c.CaseId From CaseHistory c where CaseId IN: Trigger.newMap.keyset()];
for(Case c : caseswithhistory)
{
      Case cseold = mapOldcase.get(c.Id);
      if((c.Status <> cseold.Status) && c.Status == 'Closed')
      {
            for(CaseHistory ch : c.Histories)
            {
                  if(ch.NewValue == 'Working')
                  {
                        Datetime dtWorking = ch.CreatedDate;
                  Integer diff =    dtWorking.date().daysBetween(Date.today());
                  dtWorking = dtWorking.addDays(diff);
                  Decimal hrs = (Datetime.now().getTime() - dtWorking.getTime())/1000/60/60 ;
                  Decimal mts =(Datetime.now().getTime() - dtWorking.getTime())/1000/60;
                  Decimal actmts = mts-(hrs*60);
                        System.debug(diff);
                        System.debug(hrs);
                        System.debug(actmts);
                  c.Spent_Duration__c = diff + ' days'+ hrs + ' Hours'+actmts +' Minutes';
                  lstCase.add(c);
                  //System.debug(c.Spent_Duration__c);
                  }
                 
            }
      }
}
update lstCase;
}
User-added image
  

All Answers

vmanumachu1.393650924860069E12vmanumachu1.393650924860069E12
Try the code below. It should work:

trigger trgCalculateWorkingTime on Case (after update) {
List<Case> lstCase = Trigger.new;
List<Case> caseswithhistory  = [select Status,Id, (select c.OldValue, c.NewValue, c.Id, c.Field, c.CreatedDate, c.CaseId from Histories c where c.Field = 'Status') from Case where Id IN: Trigger.newMap.keyset()];
Map<Id,Case> mapOldcase = Trigger.oldMap;
//List<CaseHistory> cseHistory = [Select c.OldValue, c.NewValue, c.Id, c.Field, c.CreatedDate, c.CaseId From CaseHistory c where CaseId IN: Trigger.newMap.keyset()];
for(Case c : caseswithhistory)
{
Case cseold = mapOldcase.get(c.Id);
if((c.Status <> cseold.Status) && c.Status == 'Closed')
{
  for(CaseHistory ch : c.Histories)
  {
   if(ch.NewValue == 'Working')
   {
    Datetime dtWorking = ch.CreatedDate;
   Integer diff = dtWorking.date().daysBetween(Date.today());
    //Decimal hrs = Decimal.valueOf((dtWorking - Datetime.now()));
    System.debug(diff);
   }
  
  }
}
}
}
sfdc@isha.ax1814sfdc@isha.ax1814
Hi vishnu,

i tried but it is  not working iam very new to salesforce.i assigrnd that diff vaklue to  a new field called difffield__c[text field].see the below code..but the value is not populating and if i want to diaplay the days and hours and minuts then how will dispaly here only will get days na.i tried with the below code what u have sent to me
trigger trgCalculateWorkingTime on Case (after update) {
List<Case> lstCase = Trigger.new;
List<Case> caseswithhistory  = [select Status,Id, (select c.OldValue, c.NewValue, c.Id, c.Field, c.CreatedDate, c.CaseId from Histories c where c.Field = 'Status') from Case where Id IN: Trigger.newMap.keyset()];
Map<Id,Case> mapOldcase = Trigger.oldMap;
//List<CaseHistory> cseHistory = [Select c.OldValue, c.NewValue, c.Id, c.Field, c.CreatedDate, c.CaseId From CaseHistory c where CaseId IN: Trigger.newMap.keyset()];
for(Case c : caseswithhistory)
{
Case cseold = mapOldcase.get(c.Id);
if((c.Status <> cseold.Status) && c.Status == 'Closed')
{
  for(CaseHistory ch : c.Histories)
  {
   if(ch.NewValue == 'New')
   {
    Datetime dtWorking = ch.CreatedDate;
   Integer diff = dtWorking.date().daysBetween(Date.today());
    //Decimal hrs = Decimal.valueOf((dtWorking - Datetime.now()));
    System.debug(diff);
    c.difffield__c=string.valueof(diff);
   }

  }
}
}
}
vmanumachu1.393650924860069E12vmanumachu1.393650924860069E12
Updated the code to include minutes and hours

trigger trgCalculateWorkingTime on Case (after update) {
List<Case> lstCase = new List<Case>();
List<Case> caseswithhistory  = [select Status,Id, (select c.OldValue, c.NewValue, c.Id, c.Field, c.CreatedDate, c.CaseId from Histories c where c.Field = 'Status') from Case where Id IN: Trigger.newMap.keyset()];
Map<Id,Case> mapOldcase = Trigger.oldMap;
//List<CaseHistory> cseHistory = [Select c.OldValue, c.NewValue, c.Id, c.Field, c.CreatedDate, c.CaseId From CaseHistory c where CaseId IN: Trigger.newMap.keyset()];
for(Case c : caseswithhistory)
{
      Case cseold = mapOldcase.get(c.Id);
      if((c.Status <> cseold.Status) && c.Status == 'Closed')
      {
            for(CaseHistory ch : c.Histories)
            {
                  if(ch.NewValue == 'Working')
                  {
                        Datetime dtWorking = ch.CreatedDate;
                  Integer diff =    dtWorking.date().daysBetween(Date.today());
                  dtWorking = dtWorking.addDays(diff);
                  Decimal hrs = (Datetime.now().getTime() - dtWorking.getTime())/1000/60/60 ;
                  Decimal mts =(Datetime.now().getTime() - dtWorking.getTime())/1000/60;
                  Decimal actmts = mts-(hrs*60);
                        System.debug(diff);
                        System.debug(hrs);
                        System.debug(actmts);
                  c.Spent_Duration__c = diff + ' days'+ hrs + ' Hours'+actmts +' Minutes';
                  lstCase.add(c);
                  //System.debug(c.Spent_Duration__c);
                  }
                 
            }
      }
}
update lstCase;
}
User-added image
  
This was selected as the best answer
sfdc@isha.ax1814sfdc@isha.ax1814
Hi Vishnu thanks alot for your help.

here the value is populating but the thing is in our scenario we can re open the case and re close teh case..in this scenario we have to show the first time difference only if we will change  the closed case to escalate or closed it shuld not refelct on this field and here when ever 2nd time iam trying to close iam getting below error.

  User-added image

suppose when status='workng ' to status='closed'  -     2mis
status='closed'  to status='working'     -  1min
status='working'  to status='closed'   -  3mis

it has to show first difference 2mis only...


regards,
Isha



vmanumachu1.393650924860069E12vmanumachu1.393650924860069E12
You could write an if condition which checks for 'c.Spent_Duration__c' is empty and if yes, it updates the value else do nothing. That way, only the first status change value would be stored in the field.
sfdc@isha.ax1814sfdc@isha.ax1814
Thank you so much vishnu it is working fine..Thanks a lot