SQL inside x++ is limited to returning values that represent the table rows. So you can apply aggregate functions on a field, as long as it returns the same datatype as the original field. As you might expect - this sucks for dates :) If you were averaging a real - no problem ;-) As it stands, I think your code below is just fine. It might look cleaner to do it in a select, but x++ wouldn't be much more efficient even if you could. At least 4.0 which I work in...
--- On Mon, 12/8/08, LarryBellouHotMail <[EMAIL PROTECTED]> wrote: From: LarryBellouHotMail <[EMAIL PROTECTED]> Subject: RE: [development-axapta] Re: datediff equivalent in X++ To: [email protected] Date: Monday, December 8, 2008, 10:56 AM Thanks all who answered so far, that helps a little. I really want to know how to do it in a select statement (if possible) like the t-sql statement below. Otherwise I have to loop through all the records like below which is just not optimal.. CustTrans ct; int days; int entries; int daysAvg; ; daysAvg=0; while select transdate, closed from ct where ct.accountnum == custTable.AccountNu m && ct.closed != datenull() && ct.amountmst > 0 && ct.transdate >= systemdateget( )-90 { days += ct.Closed - ct.TransDate; entries++; } if (entries) { if (days>0) daysAvg=days / entries; } return daysAvg; From: development- [EMAIL PROTECTED] ps.com [mailto:development- [EMAIL PROTECTED] ps.com] On Behalf Of tavocol2002 Sent: Thursday, December 04, 2008 10:10 AM To: development- [EMAIL PROTECTED] ps.com Subject: [development- axapta] Re: datediff equivalent in X++ Hello Larry. Let me see if i understand you. I have Transdate1 = 31\10\2008 And Transdate2 = 01\08\2008 You want to know how many days are between the dates??? If the answer is yes, and you want the difference. The only thing that you have to do is declare a Int variable. And assing the value of the operacion like this: int numOfDays = transdate1 - transdate2; And that's all. If this is not the answer just let me know and i will help you. Gustavo Adolfo Camargo Téllez Microsoft Dynamics AX Developer MDO Consultores Asociados S.A. Bogotá D.C. - Colombia (South America) --- In development- [EMAIL PROTECTED] ps.com <mailto:development -axapta%40yahoog roups.com> , "larrybellou" <larrybellou@ ...> wrote: > > How can I figure out the difference in # of days between two dates? > > Can it be done using a select statement as well? > > The following t-sql is what I am trying to accomplish but if you can > answer one or both of the above questions would help me get there as > well.. > > T-SQL > select avg(datediff( day, transdate, closed)) from custtrans ct (nolock) > where accountnum=' aa122'and closed > '1/1/1900' and dataareaid=' demo' > and amountmst>0 and transdate >= getdate()-90 > > select custTrans where > localCustTrans. AccountNum= ='ba368' > && localCustTrans. Closed != datenull() > && localCustTrans. AmountMST > 0 > && localCustTrans. TransDate >= systemdateget( )-90; > [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
