| Author |
 Topic  |
|
|
twan13
Starting Member
USA
4 Posts |
 Posted - 12/14/2012 : 14:26:43
|
for instance. I have table A with order blocks
and 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
India
52326 Posts |
Posted - 12/14/2012 : 14:59:32
|
use DATEDIFF
something like
DATEDIFF(n,OriginalOrderTime,GETDATE()) > =5
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
twan13
Starting Member
USA
4 Posts |
Posted - 12/14/2012 : 17:22:24
|
for instance I wrote this
select * (select blocked_orders.block_id from blocked_orders
join orders on blocked_orders.block_id = orders.block_id
where blocked_orders.order_counter >1
and 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))) >= 1
I 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
USA
4 Posts |
Posted - 12/14/2012 : 17:23:50
|
whoops, I meant this
select blocked_orders.block_id from blocked_orders
join orders on blocked_orders.block_id = orders.block_id
where blocked_orders.order_counter >1
and 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))) >= 1
how do I fix this to select orders with more than 5 minutes in spacing?
thanks so much |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 12/15/2012 : 12:21:57
|
sound like this
Not sure as we dont know how order data is present
If 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 >1
and 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 MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 12/15/2012 12:22:27 |
|
|
|
twan13
Starting Member
USA
4 Posts |
Posted - 12/17/2012 : 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 time
I hope this makes sense |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 12/19/2012 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
stumped on pulling time stamps over 5 minutes