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
 results where create date 1 minute apart
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Dmh188
Starting Member

35 Posts

Posted - 07/13/2012 :  11:46:33  Show Profile  Reply with Quote
I have a table (orderstatus). in it there is a column titled Packdate, orderid, packuser. There is no start or complete field, just that one packdate field. It Contains the date and time, down to the second.

The PackDate field is entered when the packer specifies they have packaged an order.

I need to pull up the PackUser and Orderids in which they were packed within 1 minute of each other. Also the Packdate would be nice to have.
So if user 'A' pack an order and then 10 second or 30 thirty second-- all the way up to 60 seconds laer, they pack another order. I need to show those results. like
PackUSer OrderId Packdate
A 1234 7/13/2012 08:18:35
A 12345 7/13/2012 08:19:25


Since those records for that user were created within 1 hour of each other.

I would add the coding i have been trying but it doesnt work and is really confusing. I am completely stuck on how to move forward with this. In the past it was easy because there was always a startdate field and an enddate field.

any help would be great. thank you very much

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts

Posted - 07/13/2012 :  13:08:42  Show Profile  Reply with Quote
Assuming you are on SQL 2005 or higher, you can use cross apply like this. You can also use an inner join or an exists clause in a similar way if you are on SQL 2000.
SELECT
	a.PackUser,
	a.OrderId,
	a.PackDate,
	b.OrderId AS AdjacentOrderId,
	b.PackDate AS AdjacentPackDate
FROM
	YourTable a
	CROSS APPLY
	(
		SELECT TOP 1 b.OrderId, b.PackDate
		FROM YourTable b
		WHERE b.PackUser = a.PackUser
			AND DATEDIFF(second,a.PackDate,b.PackDate) < 60
	) b;
Go to Top of Page

Dmh188
Starting Member

35 Posts

Posted - 07/13/2012 :  14:01:07  Show Profile  Reply with Quote
Thank you for the reply Sunita but that did not work and maybe i need to clarify some more information. There are multiple packers. I need it to be able to show when packer A packs an order within 60 seconds of another order that packer A also packed.
Not if packer A packed one and then packer B packed one within 60 seconds.
Also this needs to pull dates for just today. i use:
Where CONVERT(VARCHAR(8),a.packdate,101)=CONVERT(VARCHAR(8),getdate(),101)

your query returned these results:
PackUser OrderId PackDate AdjacentOrderId AdjacentPackDate
Anatoliy EDBC5D71-0F4D-46C0-83A3-000037A08F25 2011-09-06 17:43:59.017 EDBC5D71-0F4D-46C0-83A3-000037A08F25 2011-09-06 17:43:59.017
Teresa 2507EA17-966E-4D82-ACF4-0000464408C6 2012-01-19 08:39:15.970 2507EA17-966E-4D82-ACF4-0000464408C6 2012-01-19 08:39:15.970
Patricia Greer 6DE9E51A-6C5E-445F-9C0E-00012B70AA02 2012-04-06 09:30:08.063 6DE9E51A-6C5E-445F-9C0E-00012B70AA02 2012-04-06 09:30:08.063
Teresa A22E6CC5-CDA3-47A2-9E33-000203FE924C 2012-01-16 16:30:14.803 A22E6CC5-CDA3-47A2-9E33-000203FE924C 2012-01-16 16:30:14.803
Freeman 9197C46C-2638-42D0-AC1C-000240B126CC 2012-04-25 13:16:44.510 9197C46C-2638-42D0-AC1C-000240B126CC 2012-04-25 13:16:44.510

and sorry is that is hard to read, it pasted it with wordwrap. im to the point now that even if the fields being shown in the end is just the timestamp and how many orders were closed with in 60 seconds
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts

Posted - 07/13/2012 :  14:38:02  Show Profile  Reply with Quote
See if this example works for you?
CREATE TABLE #Packs
(Packuser INT, OrderId INT, PackDate DATETIME);
INSERT INTO #packs VALUES
(1,100, '2012-07-13 14:26:37.380'),
(1,101, '2012-07-13 14:26:39.380'),
(1,102, '2012-07-13 14:27:39.000'),
(1,103, '2012-07-12 14:27:39.000'),
(2,104, '2012-07-12 14:27:39.000'),
(2,105, '2012-07-13 14:27:39.000'),
(3,106, '2012-07-13 14:27:39.000'),
(4,107, '2012-07-13 14:27:39.000'),
(5,108, '2012-07-13 14:27:39.000'),
(5,109, '2012-07-13 14:26:40.000')

SELECT
	a.PackUser,
	a.OrderId,
	a.PackDate,
	b.OrderId AS AdjacentOrderId,
	b.PackDate AS AdjacentPackDate
FROM
	#packs a
	CROSS APPLY
	(
		SELECT TOP 1 b.OrderId, b.PackDate
		FROM #packs b
		WHERE b.PackUser = a.PackUser
			AND DATEDIFF(second,a.PackDate,b.PackDate) BETWEEN 0 AND 60
			AND a.orderId <> b.OrderId
			AND DATEADD(dd,DATEDIFF(dd,0,b.PackDate),0) = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
		ORDER BY b.PackDate ASC
	) b;
	
DROP TABLE #packs;
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.06 seconds. Powered By: Snitz Forums 2000