Skip to main content

Notifications

Supply chain | Supply Chain Management, Commerce
Suggested answer

Getting Data from table for specific date range

Posted on by 1,865
Hi Experts,
 
In below X++ code, smmSalesTargetTrans table store Target amount and is stored based on each month end date like 31-Jan-2024 Amount 20000 , 28-Feb-2024 Amount 30000.
The requirement is to get Target value for previous month, current week, previous week.
Currect week and previous week date is coming from parameter FromWeek, ToWeek and PrevFromWeek, PrevToWeek.
 
Difficulty I am facing to get Target value. Please help me.
 
 
 
TransDate   transDate   = contract.parmTransDate();
        FromDate   FromWeek    =   contract.parmThisWeekFrom();
        ToDate      ToWeek      =   contract.parmThisWeekTo();
        FromDate    PrevFromWeek    =   contract.parmPrevWeekFrom();
        ToDate      PrevToWeek      =   contract.parmPrevWeekTo();
        FromDate    fromDate    = dateStartMth(transDate);
        ToDate      toDate      = transDate;
        int         noOfFridayCurrMth=0;  
        int         noOfFridayPrevMth=0;
        //Previous Month
        FromDate    startPrevMthDate    =dateStartMth(prevMth(transDate));
        ToDate      endPrevMthDate      =dateEndMth(prevMth(transDate));
        //Current Month
        FromDate    startMthDate    = dateStartMth(transDate);
        ToDate      endMthDate     = dateEndMth(transDate);
		
		
		
		 HcmWorker           hcmWorker;
        smmSalesUnit        smmSalesUnit;
        DirPartyTable       dirPartyTable;
        smmSalesTarget      smmSalesTarget;
        smmSalesTargetTrans smmSalesTargetTrans;

        while select SalesPersonWorker, count(RecId)
            from smmSalesTarget
            group by smmSalesTarget.SalesPersonWorker, smmSalesUnit.ParentId, dirPartyTable.Name
            join ParentId, count(RecId)
                from smmSalesUnit
                where smmSalesUnit.SalesUnitId  == smmSalesTarget.SalesUnitId
                    && smmSalesUnit.Active      == 1
            join sum(AmountMST)
                from smmSalesTargetTrans
                where smmSalesTargetTrans.RefRecId      == smmSalesTarget.RecId
                    && smmSalesTargetTrans.TargetDate   >= startPrevMthDate //Current Month Start Date
                    && smmSalesTargetTrans.TargetDate   <= endMthDate   //Current Month End Date
            join count(RecId)
                from hcmWorker
                where hcmWorker.RecId   == smmSalesTarget.SalesPersonWorker
            join Name, count(RecId)
                from dirPartyTable
                where dirPartyTable.RecId   == hcmWorker.Person
        {
            tmpTable.initValue();
            tmpTable.SalesPersonWorker         = smmSalesTarget.SalesPersonWorker;
            //tmpTable.Product                   = smmSalesUnit.CustGroup;
            tmpTable.Region                    = smmSalesUnit.ParentId;
            
            // Current Month Target
            if (smmSalesTargetTrans.TargetDate >= startMthDate && smmSalesTargetTrans.TargetDate <= endMthDate)
          {
                tmpTable.SalesMonthlyTarget        = smmSalesTargetTrans.AmountMST;

                if (tmpTable.SalesMonthlyTarget)
                {
                    //Daily Target
                    tmpTable.SalesDailyTarget      = smmSalesTargetTrans.AmountMST/(noOfDaysCurrMth  - noOfFridayCurrMth);
   
                }
           }

            // Previous Month Target
            if (smmSalesTargetTrans.TargetDate >= startPrevMthDate && smmSalesTargetTrans.TargetDate <= endPrevMthDate)
            {
                tmpTable.SalesPrevMonthlyTarget = smmSalesTargetTrans.AmountMST;
               
            }
           
            // Current Week Target
            if (FromWeek!=dateNull() && ToWeek!=dateNull() && smmSalesTargetTrans.TargetDate >= startMthDate && smmSalesTargetTrans.TargetDate <= endMthDate)
            {
                tmpTable.SalesWeeklyTarget = (smmSalesTargetTrans.AmountMST)/4;
            }

            // Previous Week Target
            if (PrevFromWeek!=dateNull() && PrevToWeek!=dateNull() && smmSalesTargetTrans.TargetDate >= startPrevMthDate && smmSalesTargetTrans.TargetDate <= endPrevMthDate)
            {
                tmpTable.SalesPrevWeeklyTarget = (smmSalesTargetTrans.AmountMST)/4;
            }
            tmpTable.SalesMan                  = dirPartyTable.Name;
Thanks,
Faiz
  • faiz7049 Profile Picture
    faiz7049 1,865 on at
    Getting Data from table for specific date range
    Hi Expert,
     
    I need your expertise on this issue. Please
  • faiz7049 Profile Picture
    faiz7049 1,865 on at
    Getting Data from table for specific date range
    Hi Martin,
    Below X++ code and output
    while select SalesPersonWorker, count(RecId) from SmmSalesTargetTable
                group by SmmSalesTargetTable.SalesPersonWorker, SmmSalesUnitTable.ParentId
                        join ParentId, count(RecId)  from SmmSalesUnitTable
                          where SmmSalesUnitTable.SalesUnitId  == SmmSalesTargetTable.SalesUnitId
                             && SmmSalesUnitTable.Active      == 1
                         join sum(AmountMST) from SmmSalesTargetTransTable
                        where SmmSalesTargetTransTable.RefRecId==SmmSalesTargetTable.RecId
                            &&  (SmmSalesTargetTransTable.TargetDate>=startMthDate 
                            && SmmSalesTargetTransTable.TargetDate<=endMthDate)
            {
                
                tmpTable.initValue();
                tmpTable.SalesPersonWorker         = SmmSalesTargetTable.SalesPersonWorker;
                tmpTable.Region                    = SmmSalesUnitTable.ParentId;
                tmpTable.SalesMan                  = dirPartyTable.Name;
                tmpTable.SalesMonthlyTarget        = SmmSalesTargetTransTable.AmountMST;
    
               tmpTable.SalesDailyTarget      = SmmSalesTargetTransTable.AmountMST/(noOfDaysCurrMth  - noOfFridayCurrMth);
     
     
  • Martin Dráb Profile Picture
    Martin Dráb 230,064 Most Valuable Professional on at
    Getting Data from table for specific date range
    No, this isn't the output of the query. You're showing the report, while you should be debugging the select statement.
     
    You're looking at the printout and you have no idea what's wrong in your code. That proves that this isn't a helpful approach and you should consider may suggestions.
  • faiz7049 Profile Picture
    faiz7049 1,865 on at
    Getting Data from table for specific date range
    Hi Margin, 
    If you see in broken query output "Region" & "Sales Man" names are not matching with correct output.
  • faiz7049 Profile Picture
    faiz7049 1,865 on at
    Getting Data from table for specific date range
    Hi Margin,
     
     
    Above is correct query output
     
    Below is broken query output
     
  • faiz7049 Profile Picture
    faiz7049 1,865 on at
    Getting Data from table for specific date range
    Hi Martin,
    while select SalesPersonWorker, count(RecId) from SmmSalesTargetTable 
                group by SmmSalesTargetTable.SalesPersonWorker, SmmSalesUnitTable.ParentId, dirPartyTable.Name
                join ParentId, count(RecId)
                    from SmmSalesUnitTable
                    where SmmSalesUnitTable.SalesUnitId  == SmmSalesTargetTable.SalesUnitId
                        && SmmSalesUnitTable.Active      == 1
                        join count(RecId) from hcmWorker
                    where hcmWorker.RecId   == SmmSalesTargetTable.SalesPersonWorker
                join Name, count(RecId)  from dirPartyTable
                where dirPartyTable.RecId   == hcmWorker.Person
                  join sum(AmountMST) from SmmSalesTargetTransTable
             where SmmSalesTargetTransTable.RefRecId==SmmSalesTargetTable.RecId
                &&  (SmmSalesTargetTransTable.TargetDate>=startMthDate   && SmmSalesTargetTransTable.TargetDate<=endMthDate)
            {
                
                tmpTable.initValue();
                tmpTable.SalesPersonWorker         = SmmSalesTargetTable.SalesPersonWorker;
                tmpTable.Region                    = SmmSalesUnitTable.ParentId;
                tmpTable.SalesMan                  = dirPartyTable.Name;  
                tmpTable.SalesMonthlyTarget        = SmmSalesTargetTransTable.AmountMST;
    Above is working query.
     
    Broken is below
     
    while select SalesPersonWorker, count(RecId) from SmmSalesTargetTable 
                group by SmmSalesTargetTable.SalesPersonWorker, SmmSalesUnitTable.ParentId, dirPartyTable.Name
                join ParentId, count(RecId)
                    from SmmSalesUnitTable
                    where SmmSalesUnitTable.SalesUnitId  == SmmSalesTargetTable.SalesUnitId
                        && SmmSalesUnitTable.Active      == 1
                        join count(RecId) from hcmWorker
                    where hcmWorker.RecId   == SmmSalesTargetTable.SalesPersonWorker
                join Name, count(RecId)  from dirPartyTable
                where dirPartyTable.RecId   == hcmWorker.Person
                              
            {
    		           
                tmpTable.initValue();
                tmpTable.SalesPersonWorker         = SmmSalesTargetTable.SalesPersonWorker;
                tmpTable.Region                    = SmmSalesUnitTable.ParentId;
                tmpTable.SalesMan                  = dirPartyTable.Name;  
                
    			Select sum(AmountMST) from SmmSalesTargetTransTable Where SmmSalesTargetTransTable.RefRecId==SmmSalesTargetTable.RecId			
    			&&  (SmmSalesTargetTransTable.TargetDate>=startMthDate   && SmmSalesTargetTransTable.TargetDate<=endMthDate);
    			
    			tmpTable.SalesMonthlyTarget        = SmmSalesTargetTransTable.AmountMST;
     
    Thanks,
    Faiz
     
     
     
     
  • Martin Dráb Profile Picture
    Martin Dráb 230,064 Most Valuable Professional on at
    Getting Data from table for specific date range
    ​Can you tell us more about how the output differs from what you intended to get? Your description isn't clear to me and the current one ("records are duplicating to every SalesPersonWorker") sounds like a different thing than the previous description ("I am getting only one records"). We need to know what problem we're dealing with.
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 230,064 Most Valuable Professional on at
    Getting Data from table for specific date range
    Let me format your code to make is easier to read:
    while select SalesPersonWorker, count(RecId) from smmSalesTargetTable
        group by smmSalesTargetTable.SalesPersonWorker, smmSalesTargetTable.ParentId, dirPartyTable.Name
        join count(RecId) from smmSalesUnitTable
            where smmSalesUnitTable.SalesUnitId == smmSalesTargetTable.SalesUnitId
               && smmSalesUnitTable.Active      == 1
        join sum(AmountMST) from smmSalesTargetTransTable
            where smmSalesTargetTransTable.RefRecId == smmSalesTargetTable.RecId
               && smmSalesTargetTransTable.TargetDate >= startMthDate
               && smmSalesTargetTransTable.TargetDate <= endMthDate
        join count(RecId) from hcmWorker
            where hcmWorker.RecId   == smmSalesTargetTable.SalesPersonWorker
            join Name, count(RecId) from dirPartyTable
                where dirPartyTable.RecId == hcmWorker.Person
    Is this the working query or the broken one? Can you share the other one too, so we can compare them?
     
    Is all the code needed to reproduce the problem? For example, won't it behave the same if we drop hcmWorker and dirPartyTable data sources?
    while select SalesPersonWorker, count(RecId) from smmSalesTargetTable
        group by smmSalesTargetTable.SalesPersonWorker, smmSalesTargetTable.ParentId
        join count(RecId) from smmSalesUnitTable
            where smmSalesUnitTable.SalesUnitId == smmSalesTargetTable.SalesUnitId
               && smmSalesUnitTable.Active      == 1
        join sum(AmountMST) from smmSalesTargetTransTable
            where smmSalesTargetTransTable.RefRecId == smmSalesTargetTable.RecId
               && smmSalesTargetTransTable.TargetDate >= startMthDate
               && smmSalesTargetTransTable.TargetDate <= endMthDate
  • faiz7049 Profile Picture
    faiz7049 1,865 on at
    Getting Data from table for specific date range
    Hi Experts,
     
    In below X++ code, I want (SmmSalesTargetTransTable.TargetDate>=startMthDate   && SmmSalesTargetTransTable.TargetDate<=endMthDate) inside while loop because I have many date range like previous month etc. I tried many times but either records are duplicating to every SalesPersonWorker. 
     
    Please help me.
     while select SalesPersonWorker, count(RecId)
                from SmmSalesTargetTable         group by SmmSalesTargetTable.SalesPersonWorker, SmmSalesUnitTable.ParentId, dirPartyTable.Name
                join ParentId, count(RecId)
                    from SmmSalesUnitTable
                    where SmmSalesUnitTable.SalesUnitId  == SmmSalesTargetTable.SalesUnitId
                        && SmmSalesUnitTable.Active      == 1
              join sum(AmountMST) from SmmSalesTargetTransTable
             where SmmSalesTargetTransTable.RefRecId==SmmSalesTargetTable.RecId
                &&  (SmmSalesTargetTransTable.TargetDate>=startMthDate   && SmmSalesTargetTransTable.TargetDate<=endMthDate)
    
                join count(RecId)
                    from hcmWorker
                    where hcmWorker.RecId   == SmmSalesTargetTable.SalesPersonWorker
                join Name, count(RecId)
                    from dirPartyTable
                where dirPartyTable.RecId   == hcmWorker.Person
            {
                
                tmpTable.initValue();
                tmpTable.SalesPersonWorker         = SmmSalesTargetTable.SalesPersonWorker;
                tmpTable.Region                    = SmmSalesUnitTable.ParentId;
                tmpTable.SalesMan                  = dirPartyTable.Name;  
                tmpTable.SalesMonthlyTarget        = SmmSalesTargetTransTable.AmountMST;
    Thanks,
    Faiz
  • Martin Dráb Profile Picture
    Martin Dráb 230,064 Most Valuable Professional on at
    Getting Data from table for specific date range
    Looking at the final report won't tell you what happens in your code. Use the debugger to see what's going on in your code when it executes.
     
    And if you know you have a problem with a query, focus on the query. The other code, everything you have in the report and so on are irrelevant in that and it just your debugging more complicated. So, please show us the working query and the query with your unsuccessful changes.
     
    Also, verify that the expected data really exists - we can't do it for you, because we don't have access to your database.
     
     

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!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

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

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,064 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans