As far as I know Axapta does not allow you to use function calls in the WHERE clause of a select statement. You can get around that by using the select statement syntax of your back end database as follows. I am using Microsoft SQL.
static void SumByYearANDMonth(Args _args)
{
Connection con = new Connection();
Statement sqlStmt = Con.createStatement();
ResultSet SQLcustTrans;
Notes sqlStr
;
{
Connection con = new Connection();
Statement sqlStmt = Con.createStatement();
ResultSet SQLcustTrans;
Notes sqlStr
;
sqlStr = strfmt("select year(TransDate), month(TransDate), sum(AmountMST) from custTrans " +
"where month(TransDate) between %1 and %2 and year(TransDate) between %3 and %4 " +
"and DATAAREAID = '%5'" +
"group by year(TransDate), month(TransDate) " +
"order by year(TransDate), month(TransDate) " , 4, 7, 1999, 2005, 'dat');
"where month(TransDate) between %1 and %2 and year(TransDate) between %3 and %4 " +
"and DATAAREAID = '%5'" +
"group by year(TransDate), month(TransDate) " +
"order by year(TransDate), month(TransDate) " , 4, 7, 1999, 2005, 'dat');
SetPrefix('Sum By Year and Month');
SQLcustTrans = sqlStmt.executeQuery(sqlSTR);
while (SQLcustTrans.next())
{
info(strfmt('Year - %1 Month %2, Amount % 3',
SQLcustTrans.getString(1),
SQLcustTrans.getString(2),
SQLcustTrans.getString(3)));
}
}
SQLcustTrans = sqlStmt.executeQuery(sqlSTR);
while (SQLcustTrans.next())
{
info(strfmt('Year - %1 Month %2, Amount % 3',
SQLcustTrans.getString(1),
SQLcustTrans.getString(2),
SQLcustTrans.getString(3)));
}
}
If you which you could insert the resultset in a temp table in Axapta and use it in a report.
compashi <[EMAIL PROTECTED]> wrote:
compashi <[EMAIL PROTECTED]> wrote:
Hi Guys,
I have a tricky prob i want to calculate the sum of a field based on
the month and respective year.
viz. i want what is the sum that have been calculated in the month
6,7,8,9 ........... for the year 1995,1996,1997...............
or more explictly like 6/1995,7/1995,8/1995.........or
6/1996,7/1997,8/1997,9/1997.............
i am not able to jot down the sql statement with group by clause that
can fetch me the result in terms of month and year combo.
please help me out.
if you have any query as per the ques feel free to mail me at
[EMAIL PROTECTED]
Bye and have a good time to all of you.
byeeeeeeeeeeeeeeeeeeeee
Sharing the knowledge on Axapta.
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
Sharing the knowledge on Axapta.
| Yahoo! Groups Sponsor | |
|
|
Yahoo! Groups Links
- To visit your group on the web, go to:
http://groups.yahoo.com/group/Axapta-Knowledge-Village/
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
