x

how to use two calculated columns in same select statement

Hi how to rewrite the following question, here 'history' table, used_life int datatype, ul_units varchar, ul_units may contain data of firings, starts, landings, how to use two calculated columns in same select statements

here user will enter history of flights in hh and mm in two texboxes i am converting into minutes and storing, it is a simple select if it is like this

select used_life/60 as hrs,used_life%60 as mns 

but how to write this

select
case when (used_life>=60 and ul_units='HH:MM') 
     then (used_life/60) 
     else used_life end as used_life_hrs
case
when (used_life>=60 and ul_units='HH:MM')  
     then (used_life%60)  
     else used_life end as used_life_hrs
from history 

here i have to do all this conversion only when the ul_units are HH:MM and used_life>=60 else i have to print the data as it is

above query giving syntax error

can anyone help

Thanks in Advance
more ▼

asked Apr 10 '12 at 06:28 AM in Default

phani10695 gravatar image

phani10695
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

@phani10695 Please move your question to the default forum. This is only for the site discussions.

Moreover, The syntax error is there, since you are missing a comma(,) in between the columns. i.e.

  ...then (used_life/60) else used_life end as used_life_hrs,...

OR if you need to add these fields then remove naming the first column alias and add a + i.e.

  ...then (used_life/60) else used_life end +.....
If something else is desired, please let us know
more ▼

answered Apr 10 '12 at 06:50 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x24

asked: Apr 10 '12 at 06:28 AM

Seen: 592 times

Last Updated: Apr 11 '12 at 10:47 AM