December 15, 2008 at 12:43 pm
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
December 15, 2008 at 1:11 pm
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.
December 15, 2008 at 1:48 pm
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
December 16, 2008 at 4:40 pm
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
December 16, 2008 at 9:23 pm
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