Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
twan13
Starting Member
4 Posts |
Posted - 2012-12-14 : 14:26:43
|
for instance. I have table A with order blocksand table B with similar orders within those blocks that have time stamped order times.I need to find all blocked orders that have orders more than 5 minutes from the original order in that block.I am stumped with the whole timestamp part.any help would be awesome! |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-14 : 14:59:32
|
use DATEDIFFsomething likeDATEDIFF(n,OriginalOrderTime,GETDATE()) > =5------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
twan13
Starting Member
4 Posts |
Posted - 2012-12-14 : 17:22:24
|
for instance I wrote thisselect * (select blocked_orders.block_id from blocked_orders join orders on blocked_orders.block_id = orders.block_id where blocked_orders.order_counter >1and blocked_orders.user_field_6 = 'a'group by blocked_orders.block_id)having DATEDIFF(mi,(select MAX(orders.created_time)), (select MIN(orders.created_time))) >= 1I return no records though, I am confused on how to get the datediff of the orders and not the blocked order which there will only be one time stamp[/quote] |
|
|
twan13
Starting Member
4 Posts |
Posted - 2012-12-14 : 17:23:50
|
whoops, I meant thisselect blocked_orders.block_id from blocked_orders join orders on blocked_orders.block_id = orders.block_id where blocked_orders.order_counter >1and blocked_orders.user_field_6 = 'a'group by blocked_orders.block_id)having DATEDIFF(mi,(select MAX(orders.created_time)), (select MIN(orders.created_time))) >= 1how do I fix this to select orders with more than 5 minutes in spacing?thanks so much |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-15 : 12:21:57
|
sound like thisNot sure as we dont know how order data is presentIf you post some sample data from table and explain what you want as output we may be able to suggest correct soln!select blocked_orders.block_id from blocked_orders join orders on blocked_orders.block_id = orders.block_id where blocked_orders.order_counter >1and blocked_orders.user_field_6 = 'a'group by blocked_orders.block_id)having DATEDIFF(mi,MIN(orders.created_time), MAX(orders.created_time)) >= 5 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
twan13
Starting Member
4 Posts |
Posted - 2012-12-17 : 09:52:54
|
sorry new to this..so I have an orders table with columns block_id, orders_id, and created time columns. I would join this with the blocked orders table with columns block_id, and created time.Any orders within an hour of eachother are automatically merged into a blocked_order and receive the same block_id.I need to select all orders in a blocked order with a created time > 5 minutes of the blocked order created timeI hope this makes sense |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-19 : 00:56:36
|
did you try my suggestion? did it work? otherwise post some sample data and then I will be able to suggest accurate soln------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|