AX – D365FO – Multiple Aggregations in X++ Join (Group by, SQL)

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));
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s