I currently have a large SQL statement which i add the following line to in order to get the total cash for each transaction ID (which are unique):
select sum(cash) from Table a where a.branch = p.branch and a.transID = p.transID) TotalCash
and i now need to do the same but only total the cash values that have a valuedate within the last month, so i have something like this:
select sum(CASE ValueDate WHEN > @startMonthDate THEN cash ELSE NULL END) from Table a where a.branch = p.branch and a.transID = p.transID) TotalMonthCash
Sorry that I dont have the whole statement, but it is really long and specific to the context of the stored procedure but was hoping someone would know what i mean?
The SQL AGGREGATE SUM() function returns the SUM of all selected column. Applies to all values. Return the SUM of unique values. Expression made up of a single constant, variable, scalar function, or column name.
In SQL, we use the SUM() function to add the numeric values in a column. It is an aggregate function in SQL. The aggregate function is used in conjunction with the WHERE clause to extract more information from the data.
Try this instead:
SUM(CASE WHEN ValueDate > @startMonthDate THEN cash ELSE 0 END)
Explanation
Your CASE expression has incorrect syntax. It seems you are confusing the simple CASE expression syntax with the searched CASE expression syntax. See the documentation for CASE:
The CASE expression has two formats:
- The simple CASE expression compares an expression to a set of simple expressions to determine the result.
- The searched CASE expression evaluates a set of Boolean expressions to determine the result.
You want the searched CASE expression syntax:
CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END
As a side note, if performance is an issue you may find that this expression runs more quickly if you rewrite using a JOIN and GROUP BY instead of using a dependent subquery.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With