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

Mutliple sum() in X++ Join

Check values for customer DE-001 with excel

Check Mutliple sum() in X++ Join

Advertisements

Über erpcoder
Software Engineer and Dynamics AX enthusiast working in Research & Development for InsideAx

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: