Sum, Avg, Count, Max, Min functionalities in Query class in AX 2012 / D365

Hi,

Regards,

Relation between HcmPositionDetail, HcmPositionWorkerAssignment, HcmWorker for query class in AX 2012 / D365

Hi,

    Query                   query = new Query();
    QueryRun                queryRun;
    QueryBuildDataSource    qbdsHcmWorker, qbdsHcmPosition, qbdsHcmPositionDetail, qbdsHcmPositionWorkerAssign;
    QueryBuildRange         qbrWorker,qbrdDept;
    HcmPositionDetail       hcmPositionDetail;
    HcmWorker               hcmWorker;
;

    qbdsHcmWorker = query.addDataSource(tableNum(HcmWorker));

qbdsHcmWorker.addRange(fieldnum(HcmWorker,PersonnelNumber)).value('1020');

    qbdsHcmPositionWorkerAssign = qbdsHcmWorker.addDataSource(tableNum(HcmPositionWorkerAssignment));

    qbdsHcmPositionWorkerAssign.relations(false);

    qbdsHcmPositionWorkerAssign.joinMode(JoinMode::InnerJoin);

    qbdsHcmPositionWorkerAssign.addLink(fieldnum(HcmPositionWorkerAssignment,Worker),fieldnum(HcmWorker,Worker));

    //qbdsHcmPositionWorkerAssign.addRange(fieldnum(HcmPositionWorkerAssignment,ValidTo)).value(SysQuery::range(today(), dateMax()));

    qbdsHcmPositionDetail = qbdsHcmPositionWorkerAssign.addDataSource(tableNum(HcmPositionDetail));

    qbdsHcmPositionDetail.relations(false);

    qbdsHcmPositionDetail.joinMode(JoinMode::InnerJoin);
    qbdsHcmPositionDetail.addLink(fieldnum(HcmPositionWorkerAssignment,Position),fieldnum(HcmPositionDetail,Position));
    qbdsHcmPositionDetail.addRange(fieldnum(HcmPositionDetail,ValidTo)).value(date2str(dateMax(), 321, DateDay::Digits2, DateSeparator::Hyphen, DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4));

    queryRun    = new queryrun(query);

    while(queryrun.next())

    {

        hcmWorker        = queryRun.get(tableNum(HcmWorker)) as HcmWorker;

        hcmPositionDetail           = queryRun.get(tableNum(HcmPositionDetail)) as HcmPositionDetail;

        info(strFmt("%1-%2",hcmWorker.PersonnelNumber, OMOperatingUnit::findbyRecid(hcmPositionDetail.Department).Name));

    }
*****************************************************************************

display Description getWorkerPosition()
{
    HcmWorker                                    hcmWorker;
    HcmPositionWorkerAssignment   workerAssignment;
    HcmPosition                                  hcmPosition;
    HcmPositionDetail                        hcmPositionDetail;

    select worker, position from workerAssignment
       where workerAssignment.Worker == this.RecId
       join recid from hcmPosition
       where hcmPosition.RecId == workerAssignment.Position
       join position, description from hcmPositionDetail
       where hcmPositionDetail.Position == hcmPosition.RecId;

    return hcmPositionDetail.Description;
}


Regards