results where create date 1 minute apart

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

results where create date 1 minute apart

Author

Topic

Dmh188
Starting Member

37 Posts

Posted - 2012-07-13 : 11:46:33

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-13 : 13:08:42

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;

Dmh188
Starting Member

37 Posts

Posted - 2012-07-13 : 14:01:07

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-13 : 14:38:02

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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources