Max Case problem
Posted on 2005-04-19
I have a question regarding the use of Max Case.....I am trying to return some data from a database using the max case syntax, but I am struggling a little. Here is my example below.
Max (Case WHEN
((Account = '1') or(Account = '2'))
Then Sum[Expense Post Amount] Else 0 END)
as [Total Expenses]
Where ([Post Date] Between '04/01/2005' and '04/30/2005')
Account Date Expense Post Amount
1 04/02/2005 1.00
1 04/15/2005 2.00
1 04/16/2005 3.00
2 04/02/2005 1.00
I am looking to return 7.00 as [Total Expense]
I am screwing up the sum part of the code. It seems to work ok until I start trying to sum up the data that is either account 1 and or account 2.
Any suggestions or ideas would be really appreciated!!!!!!!