Referencing and sorting by calculations

  • Dear SQL Wizards,

    This is something that has been bugging me for a while and I've ever heard of a good solution for this. Please consider the following query from Northwind:

    select OrderID, OrderDate, ShippedDate, RequiredDate,

    cast((ShippedDate - OrderDate) as float) as 'ShippingWindowDays',

    cast((RequiredDate - OrderDate) as float) as 'OrderWindowDays',

    cast((ShippedDate - OrderDate) as float) / cast((RequiredDate - OrderDate) as float) as 'ShippingThreshold'

    from Orders

    where ShippedDate IS NOT NULL

    order by cast((ShippedDate - OrderDate) as float) / cast((RequiredDate - OrderDate) as float)

    This query counts the days from when an order was placed to when it was shipped, and also the days from when it was placed to when it was required, and figures out a ratio. The ratio might be used to see how a department is doing, for example.

    I wonder, is there any way to use the 'ShippingWindowDays' and 'OrderWindowDays' aliases directly to calculate the 'ShippingThreshold' column or must I redo the calculations in each column? Also, I know of the shortcut "Order by 7", for example to sort by the 7th column... is there a better way to do that?

    The reason I ask is because having duplicated code often makes my queries much more complex than they really ought to be and makes bug-fixing a much more difficult process.

    If it came down to it, would using UDFs make this type of thing much slower? (assuming the UDF didn't do any querying, of course.)

    Many thanks,

    -Steve O

  • Are you looking for somethig like this?

    select

    OrderID,

    OrderDate,

    ShippedDate,

    RequiredDate,

    ShippingWindowDays,

    OrderWindowDays

    ShippingThreshold

    from

    (

    select

    OrderID,

    OrderDate,

    ShippedDate,

    RequiredDate,

    cast((ShippedDate - OrderDate) as float) as 'ShippingWindowDays',

    cast((RequiredDate - OrderDate) as float) as 'OrderWindowDays',

    cast((ShippedDate - OrderDate) as float) / cast((RequiredDate - OrderDate) as float) as 'ShippingThreshold'

    from

    Orders

    where

    ShippedDate IS NOT NULL

    ) dt -- This is a derived table from which you then select and order your data

    order by

    ShippingThreshold

    In SQL Server 2005, you could do the same with a CTE.

  • Thanks Lynn,

    I think you answered my question about as well as possible. Sure enough, a search for CTE brought up plenty of results for how I could do this with SQL 2k5 and you provided me with a useful example of how I could do this in my world. Many thanks!

    -Steve O

  • As long as we're mentioning 2005, you can just order by the assigned name of your calculated column without bothering with a CTE. The following runs just fine on a 2005 box.

    ------------------------------------------------------------------------------------------

    create table #tempOrders (orderID int identity(1,1) primary key

    ,requiredDate datetime

    ,orderDate dateTime

    ,shippedDate datetime)

    insert into #temporders(OrderDate,requiredDate,shippedDate)

    select getdate(),getdate()+5,getdate()+10 union all

    select getdate(),getdate()+7,getdate()+10 union all

    select getdate(),getdate()+3,getdate()+10 union all

    select getdate(),getdate()+4,getdate()+10 union all

    select getdate(),getdate()+2,getdate()+10

    select orderID,orderDate,requiredDate,shippedDate

    ,cast((ShippedDate - OrderDate) as float) / cast((RequiredDate - OrderDate) as float) as 'ShippingThreshold'

    from #tempOrders

    order by ShippingThreshold

    drop table #tempOrders

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Even less calculations:

    select

    OrderID,

    OrderDate,

    ShippedDate,

    RequiredDate,

    ShippingWindowDays,

    OrderWindowDays

    ShippingWindowDays - OrderWindowDays AS ShippingThreshold

    from (

    select

    OrderID,

    OrderDate,

    ShippedDate,

    RequiredDate,

    cast((ShippedDate - OrderDate) as float) as [ShippingWindowDays],

    cast((RequiredDate - OrderDate) as float) as [OrderWindowDays]

    from Orders

    where ShippedDate IS NOT NULL

    ) dt -- derived table contains all essential calculated values

    order by ShippingThreshold

    Because ordering happens on prepared recordset you may use final column name in your ORDER BY clause.

    _____________
    Code for TallyGenerator

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply