SELECT Item, [end]-Start 'LongestVariance'
FROM YourTable t1
WHERE [end]-Start IN (SELECT MAX([end]-start) FROM YourTable t2 WHERE t1.item= t2.item GROUP BY [date], location, CheckNo)

quote:

---

Originally posted by BobsDesk

Total seconds, like 300 for 5 minutes or 600 for ten.
Also note this is a SQL 2000 server.

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by BobsDesk

Total seconds, like 300 for 5 minutes or 600 for ten.
Also note this is a SQL 2000 server.

---

then whats the base date?

so 300 sec mans 300 seconds from where? start of the day?
unless you've a bse reference you cant find time difference properly

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

---

CREATE TABLE [dbo].[CheckDetail](
	[SID] [int] NULL,
	[DOB] [datetime] NULL,
	[DayPart] [varchar](10) NULL,
	[TimeStamp] [datetime] NULL,
	[CheckNo] [int] NULL,
	[ItemID] [int] NULL,
	[ItemDescription] [nvarchar](25) NULL,
	[OrderPrepTime] [int] NULL,
	[FirstDisplayedTime] [int] NULL,
	[Cook] [int] NULL,
	[Ideal] [int] NULL,
	[IVar] [int] NULL
) ON [PRIMARY]

SID	DOB	DayPart	TimeStamp	CheckNo	ItemID	ItemDescription	OrderPrepTime	FirstDisplayedTime	Cook	Ideal	IVar
402	2012-11-04 00:00:00.000	Lunch	2012-11-04 11:58:18.157	10	2807	KID TENDERS	0	0	481	240	-241
403	2012-11-01 00:00:00.000	Lunch	2012-11-01 12:51:04.687	45	8659	INDIV HAND TOSS	922	361	922	480	-442
403	2012-11-03 00:00:00.000	Lunch	2012-11-03 13:13:39.030	42	4206	SALAD HOUSE	48	0	48	240	192
403	2012-11-05 00:00:00.000	Lunch	2012-11-05 12:27:37.530	41	8404	CHK QUESADILLA	0	0	464	420	-44
404	2012-11-03 00:00:00.000	Lunch	2012-11-03 13:27:50.650	30	7840	JALAPENO BURGER	0	0	759	336	-423
405	2012-10-31 00:00:00.000	Lunch	2012-10-31 13:16:05.350	67	2103	SESAME	0	0	252	420	168

	Delete From CheckWorst
	Declare @CSID int, @CDOB datetime, @CCheckNo int
	Declare Chks Cursor FORWARD_ONLY For
	Select Distinct SID, DOB, CheckNo
	From CheckDetail
	Group by SID, DOB, CheckNo Order by SID, DOB, CheckNo
	Open Chks
	Fetch Next From Chks Into @CSID, @CDOB, @CCheckNo
	While @@FETCH_STATUS = 0
	Begin
		Insert Into CheckWorst
		Select Top 1 *
		From CheckDetail
		Where @CSID = SID and @CDOB = DOB and @CCheckNo = CheckNo
		Order by SID, DOB, CheckNo, IVar
		Fetch Next From Chks Into @CSID, @CDOB, @CCheckNo
	End
	Close Chks Deallocate Chks

Insert Into CheckWorst
Select required columns...
from
(
Select *,row_number() over (partition by SID, DOB, CheckNo order by IVar) AS Seq
From CheckDetail
)t
WHERE seq=1

quote:

---

Originally posted by visakh16

Insert Into CheckWorst
Select required columns...
from
(
Select *,row_number() over (partition by SID, DOB, CheckNo order by IVar) AS Seq
From CheckDetail
)t
WHERE seq=1

I wish I had access to the ROW property but It's a MS SQL 2000 box

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

---