Multiple Aggregations in X++ Join

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

Check values for customer DE-001 with excel