Question: Can you use a SQL statement to calculating it!
How can I print "10 to 20" for books that require for between $10 and $20, "unknown" for books whose price is null, and "other" for all other prices?
Answer:
If Object_id ( ' DBO. table101301 ' ) Is Not Null
Drop Table DBO. table101301
Create Table DBO. table101301
(
[ Bookid ] Int Not Null ,
[ Bookname ] Varchar ( 10 ),
[ Price ] Varchar ( 10 )
)
Insert Into DBO. table101301 Values ( 1 , ' Book1 ' , 1 )
Insert Into DBO. table101301 Values ( 2 , ' Book2 ' , 5 )
Insert Into DBO. table101301 Values ( 3 , ' Book3 ' , 8 )
Insert Into DBO. table101301 Values ( 4 , ' Book4 ' , 10 )
Insert Into DBO. table101301 Values ( 5 , ' Book5 ' , 12 )
Insert Into DBO. table101301 Values ( 6 , ' Book6 ' , Null )
Insert Into DBO. table101301 Values ( 7 , ' Book7 ' , 20 )
Insert Into DBO. table101301 Values ( 8 , ' Book8 ' , 21 )
Insert Into DBO. table101301 Values ( 9 , ' Book9 ' , Null )
Select [ Bookid ] , [ Bookname ] ,
[ Price ] = (
Case
When [ Price ] Is Null Then ' Unknown '
When [ Price ] Between 10 And 20 Then ' 10 to 20 '
Else [ Price ]
End
)
From DBO. table101301
Drop Table DBO. table101301
