Count with distinct Axapta

Question

I want to get this query working in axapta

Select Count(Distinct CUSTACCOUNT), YEARMONTH
From ALT_CUSTOMERSALES As AC 
Where INVOICEDATE >= DateAdd(Day, -90, GetDate())
Group By YEARMONTH

Where ALT_CUSTOMERSALES is a view that relates SalesTable and CustInvoiceJour. I'm searching for sales to unique customers per month. I tried using the clause existjoin between the view and CustTable, but the group by isn't working. Any suggestions?

This is the code I have:

Query                   query = new Query('miQuery');
QueryBuildDataSource    vsQBDS, custTaQBDS;
QueryBuildRange         qbr1;
QueryRun                qr;

months = 3;
dPeriod = DateTimeUtil::date(DateTimeUtil::addDays(DateTimeUtil::utcNow(), months * -30));

custTaQBDS = query.addDataSource(tableNum(CustTable));

custTaQBDS.addSelectionField(fieldNum(CustTable, AccountNum), SelectionField::Count);

vsQBDS = custTaQBDS.addDataSource(tableNum(ALT_CustomerSales));

vsQBDS.fields().addField(fieldNum(ALT_CustomerSales, YearMonth));

vsQBDS.relations(false);
vsQBDS.joinMode(JoinMode::ExistsJoin);
vsQBDS.addLink(fieldNum(ALT_CustomerSales, CustAccount), fieldNum(CustTable, AccountNum));

qbr1 = vsQBDS.addRange(fieldNum(ALT_CustomerSales, InvoiceDate));
qbr1.value(strFmt("(%2 > %1)", date2StrXpp(dPeriod), fieldStr(ALT_CustomerSales, InvoiceDate)));

//vsQBDS.addGroupByField(fieldNum(ALT_CustomerSales, YearMonth));

qr = new QueryRun(query);

Do you have any idea what the underlying database is? What error message do you get? — Gordon Linoff, Aug 25 '14 at 16:22
@GordonLinoff No error, but the query does not group as I want. — Daniel Tróchez, Aug 25 '14 at 16:45

score 1 · Answer 1 · answered Aug 25 '14 at 20:30

1

It's easier if you post the generated SQL query with it.

Btw you don't need the distinct, grouping on both fields is enough.

SELECT YEARMONTH, COUNT(CustAccount)
FROM ALT_CUSTOMERSALES as ac
WHERE INVOICEDATE >= ''
GROUP BY YEARMONTH, CUSTACCOUNT

answered Aug 25 '14 at 20:30

Reinard

3,624
10
43
61

Thanks for your reply. I need an avg of customer per month, in other words:`while select count(AccountNum) from custTable exists join CustAccount, InvoiceDate, YearMonth from alt_CustomerSales group by YearMonth where viewSales.InvoiceDate > dPeriod && custTableAVG.AccountNum == viewSales.CustAccount` – Daniel Tróchez Aug 25 '14 at 21:40

Count with distinct Axapta

1 Answers1