1

I am trying to come up with a sql statement which converts the odometer if stored in km to miles. If the odometer is stored in miles, it leaves as it is. After the conversion, it then needs to check for Search paramters i.e Mileage.

The steps I have taken is using the Case Statement.

Here is my snippet of the select statement that I am using currently:

DECLARE 
@Mileage NVARCHAR(75) = NULL,
@IsMiles BIT = 1,
@Converted NVARCHAR(75) = NULL
SELECT [Id],Odometer,IsMiles,
CASE IsMiles when 0 THEN OdometerValue * 0.62137
else Odometer end 
FROM [dbo].[Vehicle]
where IsMiles = 0

Is there anyway to pass the Result of the case statement to ConvertedOdometer. I want to use that value to evaluate against the search Mileage parameters.

Something like this with this condition:

(ConvertedOdometer >=0 AND ConvertedOdometer <= @Mileage)

I am new to Case statement so have used these guides:

StackOverflow

Sql School

Some Blog

Community
  • 1
  • 1
Yuvi
  • 528
  • 8
  • 18
  • You can just say CASE ... END AS 'ConvertedOdometer' – RichardCL May 04 '16 at 14:14
  • @RichardCL thanks. I was putting the AS keyword on a wrong place. That solves the issue – Yuvi May 04 '16 at 14:27
  • It's so easy to do! :-) Have a look at Microsoft SQL Server 2012 T-SQL Fundamentals by Itzik Gen-Gan > Chapter 5: Table Expressions > Derived Tables > Assigning Column Aliases. – RichardCL May 04 '16 at 14:33
  • 1
    Slightly off topic but worth mentioning; SQL doesn't have a case statement. It has a [case expression](https://msdn.microsoft.com/en-us/library/ms181765.aspx). The difference being statements control the flow of a language (think if, while, do, etc), while an expression returns a value. – David Rushton May 04 '16 at 14:55

1 Answers1

2

Perhaps something like this ...

DECLARE 
@Mileage NVARCHAR(75) = NULL,
@IsMiles BIT = 1,
@Converted NVARCHAR(75) = NULL

select a.* from 
(SELECT [Id],Odometer,IsMiles,
CASE when IsMiles=0 THEN OdometerValue * 0.62137 else Odometer end as ConvertedOdometer 
FROM [dbo].[Vehicle]
where IsMiles = 0)a
where a.ConvertedOdometer >=0 AND 
a.ConvertedOdometer <= @Mileage
cableload
  • 4,215
  • 5
  • 36
  • 62