CODEWITHSUNDEEP
sqlsql-server
Blake Rivell
Blake Rivell

Reputation: 13895

When the value can either be dollar or percent for the same field which field type should I use in sqlserver

I have a scenario where I want to store dollar amounts or percentages in the same field in SqlServer.

I have a Type field which takes a value such as 'dollar' or 'percent' and then a value field which takes either type of value: 25.00 or 0.05.

If this is a terrible idea I would like to know, because then I will split the fields out.

Should I be using decimal or float for this type of field and why?

Upvotes: 0

Views: 314

Answers (1)

StackUser
StackUser

Reputation: 5398

If this is a terrible idea I would like to know, because then I will split the fields out.

All depends on your design but not a terribe thing. Even if you split the columns at any point of time one column would be filled but the other column should be 0 or Null.

Should I be using decimal or float for this type of field and why?

Use can use Decimal refer the example below.

Declare @table table (
    Type VARCHAR(50)
    ,Value DECIMAL(18, 2)
    )

Insert into @table
values ('$',25.00),
('%',0.05),
('$',25.00)

You can do sum or average using 

SELECT sum(value) AS Sum
    ,avg(value) AS Avg
FROM @table
WHERE type = '$'

Upvotes: 1

Related Questions