Supply chain | Supply Chain Management, Commerce

Getting Data from table for specific date range

(1) Share

Report

Posted on by faiz7049

1,883

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

Categories:

Dynamics 365 Supply Chain Management

All responses (17)

Answers (0)

faiz7049 1,883 on at

Like (0)

Report

Getting Data from table for specific date range

Hi Expert,

I need your expertise on this issue. Please
faiz7049 1,883 on at

Like (0)

Report
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 230,445 Most Valuable Professional on at

Like (0)

Report

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 1,883 on at

Like (0)

Report

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 1,883 on at

Like (0)

Report

Getting Data from table for specific date range

Hi Margin,

Above is correct query output

Below is broken query output
faiz7049 1,883 on at

Like (0)

Report
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 230,445 Most Valuable Professional on at

Like (0)

Report

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 230,445 Most Valuable Professional on at

Like (0)

Report
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 1,883 on at

Like (0)

Report
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 230,445 Most Valuable Professional on at

Like (0)

Report

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.