CODEWITHSUNDEEP
sqlt-sql
user441365
user441365

Reputation: 4032

T-SQL sorting by a calculated column

I have a table with these columns: ID, Price, IsMonthlyPrice

How can I select all and sort by weekly price, taking into account that:

if (IsMonthlyPrice = false) then Price is weekly

EDIT:

I need to sort by weekly price

weekly price of a monthly price would be: Price*12/52

Upvotes: 5

Views: 16582

Answers (5)

Sam Sussman
Sam Sussman

Reputation: 1045

I generally wrap the select in a another one. then the column is no longer "calculated"

select 
    Price,
    ID
from 
(select 
        ID,
        Price,
        [calculate the IsMonthly Price] as 'IsMonthlyPrice'
    from table
) order by IsMonthlyPrice

Upvotes: 0

Po-ta-toe
Po-ta-toe

Reputation: 732

You don't need to include the calculation twice. You can order on the column number

SELECT 
    ID, 
    Price, 
    IsMonthlyPrice, 
    CASE IsMonthlyPrice
    WHEN 1 THEN Price * 12 / 52
    ELSE price
    END
FROM [TABLE]
    order by 
        4

Upvotes: 18

Guffa
Guffa

Reputation: 700572

You can use a case to calculate different values depending on whether the price is weekly or monthly.

This will calculate an approximate (assuming 31 days per month) price per day to sort on:

select ID, Price, IsMonthlyPrice
from TheTable
order by Price / case IsMonthlyPrice when 1 then 31.0 else 7.0 end

Upvotes: 0

Shan Plourde
Shan Plourde

Reputation: 8726

You can sort calculated columns using two approaches. The more verbal approach (untested pseudocode) is:

select ID, CASE WHEN IsMonthlyPrice = 0 THEN Price * 52 / 12 ELSE Price END AS Price
ORDER BY CASE WHEN IsMonthlyPrice = 0 THEN Price * 52 / 12 ELSE Price END

The more concise approach is:

select * from
(
select ID, CASE WHEN IsMonthlyPrice = 0 THEN Price * 52 / 12 ELSE Price END AS Price
) as derived
ORDER BY derived.Price

Upvotes: 3

jjmontes
jjmontes

Reputation: 26934

Not sure if this is what you look for but maybe try:

select 
    id,
    case isMonthlyPrice when 'false' then (price*52/12) else price end normalizedPrice
from
    yourTable
order by
    normalizedPrice

Upvotes: 4

Related Questions