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

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo di WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione /  Modifica )

Google photo

Stai commentando usando il tuo account Google. Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione /  Modifica )

Connessione a %s...