Dynamics Community Forum Thread Details

Getting Data from table for specific date range

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:

Supply Chain Management

Quick Links

Leaderboard