stumped on pulling time stamps over 5 minutes

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

stumped on pulling time stamps over 5 minutes

Author

Topic

twan13
Starting Member

4 Posts

Posted - 2012-12-14 : 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

52326 Posts

Posted - 2012-12-14 : 14:59:32

use DATEDIFF

something like

DATEDIFF(n,OriginalOrderTime,GETDATE()) > =5

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

twan13
Starting Member

4 Posts

Posted - 2012-12-14 : 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

4 Posts

Posted - 2012-12-14 : 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

52326 Posts

Posted - 2012-12-15 : 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/

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 time

I 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 MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources