最新消息:ww12345678 的部落格重装上线,希望大家继续支持。

Multiple Aggregations in X++ Join

网络文摘 William 1651浏览 0评论

At work we’ve recently discussed how to calculate a customers invoice amount for multiple years. One idea was to loop over the customer accounts (while select)  and individually calculate the sum for each year with individual statements (select sum). However, this can be done by a single statement.

CustInvoiceJour     custInvoiceJour;
CustInvoiceTrans    custInvoiceTrans1;
CustInvoiceTrans    custInvoiceTrans2;

while select InvoiceAccount,InvoiceId from custInvoiceJour
    group by InvoiceAccount
    outer join InvoiceId, sum(LineAmount) from custInvoiceTrans1
        where custInvoiceJour.InvoiceId == custInvoiceTrans1.InvoiceId &&
                custInvoiceTrans1.InvoiceDate >= str2DateDMY(‘1.1.2011′) &&
                custInvoiceTrans1.InvoiceDate <= str2DateDMY(‘31.12.2011′)
    outer join InvoiceId, sum(LineAmount) from custInvoiceTrans2
        where custInvoiceJour.InvoiceId == custInvoiceTrans2.InvoiceId &&
                custInvoiceTrans2.InvoiceDate >= str2DateDMY(‘1.1.2012′) &&
                custInvoiceTrans2.InvoiceDate <= str2DateDMY(‘31.12.2012′)
{
    info( strFmt("Customer:%1 2011:%2 2012:%3",  
                  custInvoiceJour.InvoiceAccount,
                  custInvoiceTrans1.LineAmount,
                  custInvoiceTrans2.LineAmount));
}
//Customer:DE-001 2011:3.814.145,50 2012:3.842.223,00

This will result in the following output

Mutliple sum() in X++ Join

Check values for customer DE-001 with excel

Check Mutliple sum() in X++ Join


发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址