Reputation: 2096

" If condition" as one of the constraints to a Column Field?

I have a Sales tax column in one of my SQL Table where Data is stored.

I was wondering is it possible to place an constraint on the table itself for the Sales Tax

Column saying " if > June 2008 tax is 2 % " else "tax is 4 %"?

Should that come from stored procs related to that Table?

Upvotes: 0

Answers (5)

JuniorFlip

Reputation: 417

here is a simple way you can alter or create a new table

 create table #test (
Date datetime,
amount money,
tax as case when date > '06/01/08' then convert(decimal(10,2),4.00) else convert(decimal(10,2),2.00) end,
)

insert into #test
select '05/01/08', 10.00

insert into #test
select '07/01/08', 10.00

select * from #test

Upvotes: 0

Callie J

Reputation: 31296

If you want to make sure that the column 'tax' is 2 or 4 depending on the month (e.g., month 9 = September), then you could do this:

ALTER TABLE SomeTable 
   ADD CONSTRAINT CK_SalesTax
   CHECK ((MONTH(GETDATE()) = 9 AND SalesTax = 2) OR (MONTH(GETDATE()) != 9 AND SalesTax = 4))

Obviously, vary for your conditions. e.g., to test for dates after June 2008, it's a bit simpler.

(GETDATE() >= '1 June 2008' AND SalesTax = 2)

You should be able to build this into a CHECK constraint of using a similar mechanism as I've dropped in the first example.

Note this only checks the value that put in to the table. It won't auto-populate. As other people have noted, if you want auto-population, you need a trigger.

Upvotes: 1

Mayo

Reputation: 10792

You could use a constraint to achieve this effect...

pseudo-SQL...

(YourDate >= '6/1/08' and YourTaxData = 0.02) or (YourTaxData = 0.04)

You might consider instead using a table to host the tax values and using your queries to pull the appropriate tax value for a given date/location. That's more scalable than the constraint.

Upvotes: 1