CODEWITHSUNDEEP
sqlsql-serversql-server-2012sqldatatypes
aluminum_starch
aluminum_starch

Reputation: 93

SQL Server 2012 Money Data Type Issues

I am using this formula 

(p.Price - i.Price) * q.Qty

in SQL Server 2012. There are p.Price values of 0 or null. The data type for the p.Price and i.Price is money, the q.Qty is int.

So, if the calculation is (0-10.00) * 50 = -500.00 (it's a negative number because it is product that are bought on a schedule) but I always get 0 or null as a result in my data table.

Update : 

 (sum(p.Price - i.Price) * q.Qty)

but I am getting an invalid in the select list because it is not contained in either an aggregate or the group by clause ?

Upvotes: 1

Views: 2076

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32693

In SQL Server money type can be negative. It has this range:

-922,337,203,685,477.5808 to 922,337,203,685,477.5807

Check your data and formula, it should be working as expected:

DECLARE @m1 money = 0;
DECLARE @m2 money = 10;
DECLARE @qty int = 50;

SELECT (@m1 - @m2) * @qty

Result

(No column name)
-500.00

If the values can be NULL, you can use ISNULL to turn them into zeros:

DECLARE @m1 money = NULL;
DECLARE @m2 money = 10;
DECLARE @qty int = 50;

SELECT (ISNULL(@m1,0) - ISNULL(@m2, 0)) * ISNULL(@qty, 0)

Result

(No column name)
-500.00

Upvotes: 2

Related Questions