SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 stumped on pulling time stamps over 5 minutes
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

twan13
Starting Member

USA
4 Posts

Posted - 12/14/2012 :  14:26:43  Show Profile  Reply with Quote
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
52309 Posts

Posted - 12/14/2012 :  14:59:32  Show Profile  Reply with Quote
use DATEDIFF

something like

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

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

Go to Top of Page

twan13
Starting Member

USA
4 Posts

Posted - 12/14/2012 :  17:22:24  Show Profile  Reply with Quote
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]
Go to Top of Page

twan13
Starting Member

USA
4 Posts

Posted - 12/14/2012 :  17:23:50  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/15/2012 :  12:21:57  Show Profile  Reply with Quote
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
Go to Top of Page

twan13
Starting Member

USA
4 Posts

Posted - 12/17/2012 :  09:52:54  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/19/2012 :  00:56:36  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000