Skip to main content

Notifications

Supply chain | Supply Chain Management, Commerce
Answered

Difficulty to get date difference

Posted on by 1,853
Hi Experts,
 
I am facing difficulty to get difference (workflowTrackingTableNew.CreatedDateTime - workflowTrackingTable.CreatedDateTime) in X++ code. I do not find in which place I should put. I can get this in SSRS expression but problem is if have to sum up (workflowTrackingTableNew.CreatedDateTime - workflowTrackingTable.CreatedDateTime) the divide number of purchase order. SSRS expression not allowed me.  The last column expression is =DateDiff("d",Fields!PRWFDate.Value,Fields!POWFDate.Value)
 
 
 
 
 public void processReport()
        {   
            contract = this.parmDataContract();
            TransDate reportDate = contract.parmFromDate();
            VendGroupId vendGroup = contract.parmVendGroup();
            Timezone daxTimeZone = DateTimeUtil::getUserPreferredTimeZone();

            PurchReqTable purchReqTable;
            PurchTable purchTable;
            PurchReqLine purchReqLine;
            PurchLine purchLine;
            VendGroup vendGroupTable;
            WorkflowTrackingTable workflowTrackingTable, workflowTrackingTableNew;
            WorkflowTrackingStatusTable workflowTrackingStatusTable, workflowTrackingStatusTableNew;
            WorkflowTrackingCommentTable workflowTrackingCommentTable, workflowTrackingCommentTableNew;

            integer poCount;
            AmountMST dateDiffSum,totalDayDiff;

            delete_from tmpTable;

            // Loop through purchase requisitions within the specified date range
            while select PurchReqId, CreatedDateTime
    from purchReqTable
    where purchReqTable.RequisitionStatus == PurchReqRequisitionStatus::Closed
       && (purchReqTable.CreatedDateTime >= datetobeginUtcDateTime((reportDate - 7), daxTimeZone)
       && purchReqTable.CreatedDateTime <= datetoendUtcDateTime(reportDate, daxTimeZone))
            {
                tmpTable.clear();
                tmpTable.PRId = purchReqTable.PurchReqId;
                tmpTable.PRDate = DateTimeUtil::date(purchReqTable.CreatedDateTime);

                // Fetch workflow tracking for purchase requisitions
                while select RecId, User, CreatedDateTime, TrackingContext
        from workflowTrackingTable Order by CreatedDateTime desc
                where workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval
        exists join workflowTrackingStatusTable
              where workflowTrackingStatusTable.RecId == workflowTrackingTable.WorkflowTrackingStatusTable
              && workflowTrackingStatusTable.ContextRecId == purchReqTable.RecId
              && workflowTrackingStatusTable.ContextTableId == tableNum(PurchReqTable)
                {
                    tmpTable.PRUser = workflowTrackingTable.User;
                    tmpTable.PRWFDate = workflowTrackingTable.CreatedDateTime;
                    tmpTable.PRTrackingContext = workflowTrackingTable.TrackingContext;
                }

                // Loop through purchase requisition lines to find corresponding purchase orders
                while select PurchId
        from purchReqLine
        where purchReqLine.PurchReqTable == purchReqTable.RecId
                {
                    // Find corresponding purchase order
                    select PurchId, CreatedDateTime
            from purchTable
            where purchTable.PurchId == purchReqLine.PurchId
               exists join vendGroupTable
                 where vendGroupTable.VendGroup == purchTable.VendGroup
                    && vendGroupTable.VendGroup == vendGroup;
                    {

                       
                        dateDiffSum = DateTimeUtil::date(purchTable.CreatedDateTime) - DateTimeUtil::date(purchReqTable.CreatedDateTime);
                    if(purchTable.RecId>0)
                    {
                        // Calculate the difference in days between PO and PR creation dates
                        if ((vendGroup == 'LOCAL' && dateDiffSum > 0) || (vendGroup == 'FOREIGN' && dateDiffSum > 0))
                       // if ((vendGroup == 'LOCAL') || (vendGroup == 'FOREIGN'))
                        {
                            tmpTable.POId = purchTable.PurchId;
                            tmpTable.PODate = DateTimeUtil::date(purchTable.CreatedDateTime);
                            tmpTable.DayDiff = dateDiffSum;

                            // Fetch workflow tracking for purchase orders
                            while select RecId, User, CreatedDateTime, TrackingContext
                    from workflowTrackingTableNew order by CreatedDateTime asc
                                where workflowTrackingTableNew.TrackingType == WorkflowTrackingType::Submission
                    exists join workflowTrackingStatusTableNew
                          where workflowTrackingStatusTableNew.RecId == workflowTrackingTableNew.WorkflowTrackingStatusTable
                          && workflowTrackingStatusTableNew.ContextRecId == purchTable.RecId
                          && workflowTrackingStatusTableNew.ContextTableId == tableNum(PurchTable)
                            {
                                tmpTable.POUser = workflowTrackingTableNew.User;
                                tmpTable.POWFDate = workflowTrackingTableNew.CreatedDateTime;
                                tmpTable.POTrackingContext = workflowTrackingTableNew.TrackingContext;
                                //  tmpTable.TotalDayDiff = workflowTrackingTable.CreatedDateTime;
                            }
                        }
                    }
                    }
                }

                tmpTable.insert(); // Insert the record after processing all data
            }

        }
Thanks,
Faiz
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 229,993 Most Valuable Professional on at
    Difficulty to get date difference
    You can use DateTimeUtil::getDifference() to get a difference between two date/time values. The result is the number of seconds.
     
    Another approach is utilizing the .NET base class library, namely Subtract() method of System.DateTime. This will give you a System.TimeSpan object, which has properties like Days.
    #TimeConstants
    utcDateTime a = DateTimeUtil::newDateTime(mkDate(1,9,2024), 8*#SecondsPerHour);
    utcDateTime b = DateTimeUtil::newDateTime(mkDate(5,9,2024), 13*#SecondsPerHour);
    
    System.DateTime bNet = b;
    System.TimeSpan diff = bNet.Subtract(a);
    info(strFmt("%1 days, %2 hours", diff.Days, diff.Hours));
  • faiz7049 Profile Picture
    faiz7049 1,853 on at
    Difficulty to get date difference
    Hi Martin,
     
    How to get with time. Please
  • Martin Dráb Profile Picture
    Martin Dráb 229,993 Most Valuable Professional on at
    Difficulty to get date difference
    You explicitly drop the time by DateTimeUtil::date(). This method takes a date/time value and returns a date only.
  • faiz7049 Profile Picture
    faiz7049 1,853 on at
    Difficulty to get date difference
    Hi Martin,
     
    Problem was workflowTrackingTable.CreatedDateTime picking wrong date inside loop. I stored it in local variable PRWFDate then use in loop.
    Now "Total day" date difference is based on date. I want this based on DateTime. If you see 1st row below screenshot "Total days diff with time" =0 but "Total day" =1.00
     
    How to include time as well.
     
     
     
     
     
    while select RecId, User, CreatedDateTime, TrackingContext
            from workflowTrackingTable Order by CreatedDateTime desc
                    where workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval
            exists join workflowTrackingStatusTable
                  where workflowTrackingStatusTable.RecId == workflowTrackingTable.WorkflowTrackingStatusTable
                  && workflowTrackingStatusTable.ContextRecId == purchReqTable.RecId
                    && workflowTrackingStatusTable.ContextTableId == tableNum(PurchReqTable)
                    {
                        tmpTable.PRUser = workflowTrackingTable.User;
                        tmpTable.PRWFDate = workflowTrackingTable.CreatedDateTime;
                        tmpTable.PRTrackingContext = workflowTrackingTable.TrackingContext;
                        PRWFDate=workflowTrackingTable.CreatedDateTime;
                    }
    
                    // Loop through purchase requisition lines to find corresponding purchase orders
                    while select PurchId
            from purchReqLine
            where purchReqLine.PurchReqTable == purchReqTable.RecId
                    {
                        // Find corresponding purchase order
                        select PurchId, CreatedDateTime
                from purchTable
                where purchTable.PurchId == purchReqLine.PurchId
                   exists join vendGroupTable
                     where vendGroupTable.VendGroup == purchTable.VendGroup
                        && vendGroupTable.VendGroup == vendGroup;
                        {
    
                           
                            dateDiffSum = DateTimeUtil::date(purchTable.CreatedDateTime) - DateTimeUtil::date(purchReqTable.CreatedDateTime);
                        if(purchTable.RecId>0)
                        {
                            // Calculate the difference in days between PO and PR creation dates
                            if ((vendGroup == 'LOCAL' && dateDiffSum > 0) || (vendGroup == 'FOREIGN' && dateDiffSum > 0))
                           // if ((vendGroup == 'LOCAL') || (vendGroup == 'FOREIGN'))
                            {
                                tmpTable.POId = purchTable.PurchId;
                                tmpTable.PODate = DateTimeUtil::date(purchTable.CreatedDateTime);
                                tmpTable.DayDiff = dateDiffSum;
    
                                // Fetch workflow tracking for purchase orders
                                select RecId, User, CreatedDateTime, TrackingContext
                        from workflowTrackingTableNew order by CreatedDateTime asc
                                    where workflowTrackingTableNew.TrackingType == WorkflowTrackingType::Submission
                        exists join workflowTrackingStatusTableNew
                              where workflowTrackingStatusTableNew.RecId == workflowTrackingTableNew.WorkflowTrackingStatusTable
                              && workflowTrackingStatusTableNew.ContextRecId == purchTable.RecId
                                    && workflowTrackingStatusTableNew.ContextTableId == tableNum(PurchTable);
                                {
                                    tmpTable.POUser = workflowTrackingTableNew.User;
                                    tmpTable.POWFDate = workflowTrackingTableNew.CreatedDateTime;
                                    tmpTable.POTrackingContext = workflowTrackingTableNew.TrackingContext;
                                    tmpTable.TotalDayDiff=workflowTrackingTableNew.CreatedDateTime-workflowTrackingTable.CreatedDateTime;
                                    tmpTable.UTCDayDiff=DateTimeUtil::date(workflowTrackingTableNew.CreatedDateTime)-DateTimeUtil::date(PRWFDate);
                                    
                                    
                                }
                            }
                        }
                        }
                    }
    
                    tmpTable.insert(); // Insert the record after processing all data
                }
    
    Thanks,
    Faiz
  • Martin Dráb Profile Picture
    Martin Dráb 229,993 Most Valuable Professional on at
    Difficulty to get date difference
    Please tell us more about the problem.
    Are you saying that you're calculating the value incorrectly, or that you've assigned the correct value to tmpTable.UTCDayDiff and you just want to format the number differently in SSRS?
  • faiz7049 Profile Picture
    faiz7049 1,853 on at
    Difficulty to get date difference
    Thank you Martin,
     
    I have update the X++ code but value is not in same format dateDiffSum and UTCDayDiff. Both type is real.
     
    How to correct it in X++ code.
     
     
     
     
      dateDiffSum = DateTimeUtil::date(purchTable.CreatedDateTime) - DateTimeUtil::date(purchReqTable.CreatedDateTime);
                        if(purchTable.RecId>0)
                        {
                            // Calculate the difference in days between PO and PR creation dates
                            if ((vendGroup == 'LOCAL' && dateDiffSum > 0) || (vendGroup == 'FOREIGN' && dateDiffSum > 0))
                           // if ((vendGroup == 'LOCAL') || (vendGroup == 'FOREIGN'))
                            {
                                tmpTable.POId = purchTable.PurchId;
                                tmpTable.PODate = DateTimeUtil::date(purchTable.CreatedDateTime);
                                tmpTable.DayDiff = dateDiffSum;
    
                                // Fetch workflow tracking for purchase orders
                                select RecId, User, CreatedDateTime, TrackingContext
                        from workflowTrackingTableNew order by CreatedDateTime asc
                                    where workflowTrackingTableNew.TrackingType == WorkflowTrackingType::Submission
                        exists join workflowTrackingStatusTableNew
                              where workflowTrackingStatusTableNew.RecId == workflowTrackingTableNew.WorkflowTrackingStatusTable
                              && workflowTrackingStatusTableNew.ContextRecId == purchTable.RecId
                                    && workflowTrackingStatusTableNew.ContextTableId == tableNum(PurchTable);
                                {
                                    tmpTable.POUser = workflowTrackingTableNew.User;
                                    tmpTable.POWFDate = workflowTrackingTableNew.CreatedDateTime;
                                    tmpTable.POTrackingContext = workflowTrackingTableNew.TrackingContext;
                                    tmpTable.UTCDayDiff=DateTimeUtil::date(workflowTrackingTableNew.CreatedDateTime)-DateTimeUtil::date(workflowTrackingTable.CreatedDateTime);
                                    //  tmpTable.TotalDayDiff = workflowTrackingTable.CreatedDateTime;
                                }
                            }
     
  • Martin Dráb Profile Picture
    Martin Dráb 229,993 Most Valuable Professional on at
    Difficulty to get date difference
    The only place where you have workflowTrackingTableNew available is the last while select; the block of code on lines 82-85.
     
    A strange thing that you execute a while loop and keep overwriting the same fields. Effectively, you get values from the last record; fetching all the other records is a waste of resources.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,151 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,993 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans