using Aggregate (Sum) function and Group By Month

Reply

Jeffer Posted: 06-20-2008 14:37

rated by 0 users

Hi

I have the following InventTrans table and needs to generate inquiry screen based on the parameters passed (ItemId, Date)
The InventTrans has ItemId, Date, Qty, Status

Month | Sold Qty  |        Purchased Qty |

01      |   100        |        200                  |
02      |    200       |        300                  |

"Sold Qty" should be calculated if Status = "Sold"
"Purchased Qty" should be calculated if Status = "Purchased"

 Here is my bunch of questions :(

1. How do I implement this. I noticed there is no "SELECT CASE WHEN" syntax in Dynamics AX
2. Is it possible to get the above output in one query?

3. If not, Can i calculate the "Sold Qty" & "Purchased Qty" in a separate method and display in inquiry screen?
4. Is it possible to get year(Date) / mthofyr(Year) inside SQL Statements. (giving error)
5. How do I would like to use month(year) in group by clause?

 Would appreciate if someone helps me. Pls let me know if its not clear (sjafars@yahoo.com)

Thanks
J

Reply

Harish replied on 06-30-2008 15:02

rated by 0 users

Hello Jeffer,

You can use queries for this purpose.  The great news is - you can also use expressions here.   For more info please refer here - http://www.axaptapedia.com/Expressions_in_query_ranges

I hope this helps.  Please let us know if you are unclear about anything. 

Regards,

Harish Mohanbabu Long way to go before I sleep ..

Reply

Jeffer replied on 06-30-2008 16:13

rated by 0 users

Thanks for your reply Harris

I had look at the link you sent. For my requirement, I thought of having my resultset in a temporary table. Could you please guide me to how to do it using temp table?

Jeffer