static void Test_Query_CustTrans_with_Grouping(Args _args)
{
Query query,queryParam;
QueryRun qr;
QueryBuildDataSource qbdsCustTransSum,qbdsQuery;
CustTrans _custTrans;
TransDate toDate;
toDate = str2Date('31102016',123);
if (toDate == dateNull())
{
toDate = DateTimeUtil::date(DateTimeUtil::utcNow());
}
query = new query();
qbdsCustTransSum = query.addDataSource(tableNum(CustTrans));
qbdsCustTransSum.addRange(fieldNum(CustTrans, AccountNum)).value(queryValue(strFmt('1051')));
qbdsCustTransSum.addRange(fieldNum(CustTrans, TransDate)).value(SysQuery::range(dateNull(), toDate));
qbdsCustTransSum.orderMode(ordermode::GroupBy);
qbdsCustTransSum.addGroupByField(fieldnum(CustTrans, AccountNum));
qbdsCustTransSum.addGroupByField(fieldnum(CustTrans, TransDate));
qbdsCustTransSum.addSelectionField(fieldNum(CustTrans, AmountMST), SelectionField::Sum);
query.addHavingFilter(qbdsCustTransSum, fieldStr(CustTrans, AmountMST), AggregateFunction::Sum).value(SysQuery::valueNot('0'));
qr = new QueryRun(query);
while(qr.next())
{
_custTrans = qr.get(tableNum(CustTrans));
info(strFmt('%1 %2',_custTrans.AmountMST,_custTrans.TransDate));
}
}
No comments:
Post a Comment