CODEWITHSUNDEEP
sqlsql-serversql-server-2008
chachi
chachi

Reputation: 239

SQL IF statement?

I am working on a query and part of the query must return values based on when the transaction occurred.

A simpler version of the table would be as follows

TransDate | TransAmt    
--------------------
6/1/2012  | 10
7/5/2012  | 15
6/1/2013  | 15
7/1/2013  | 15

The restriction is that any Transactions that occurred before(TransDate) 6/30/2012 would return the full TransAmt. If the transaction happened after 6/30/2012, the value for TransAmt must be returned as 0.

I believe I would need to use the CAST function as well as the CASE function but I am not experienced with either functions. Any help or guidance would be much appreciated.

I am using SQL Server 2008 as well.

Upvotes: 0

Views: 81

Answers (3)

potashin
potashin

Reputation: 44581

You can try with case:

select case when TransDate < '6/30/2012' then TransAmt else 0 end
from tbl

SQLFiddle

Upvotes: 3

user1347948
user1347948

Reputation:

Here is what the CASE statement would look like:

SELECT
    (CASE WHEN TransDate <= '2012-06-30' THEN TransAmt
          WHEN TransDate > '2012-06-30' THEN 0
        END)
 FROM Table_1

You may want to remove the = depending on whether or not you want 2012-06-30 to be included or not.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269533

You can use case:

select (case when TransDate < '2012-06-30' then TransAmt else 0 end)
. . .

I would strongly encourage you to use ISO standard date formats (YYYY-MM-DD).

Upvotes: 4

Related Questions