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
 Distinct Max Value with Details
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BobsDesk
Starting Member

11 Posts

Posted - 11/05/2012 :  17:46:21  Show Profile  Reply with Quote
I have a table of transactions where the location number are specific retail outlets that record an item and a start and end time in seconds
Table Items:
Date datetime
Location int
CheckNo int
Item varchar(10)
Start int
End int

I want to Select the Longest (Max(End - Start)) variance between Start and End by date, location and check number as a single record but also include the item name in the result. I'm currently getting multiple records if there is a tie in the variance.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/05/2012 :  20:58:56  Show Profile  Reply with Quote
How are values currently in Start and End fields? its integer field so what does values represent?

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

Go to Top of Page

BobsDesk
Starting Member

11 Posts

Posted - 11/05/2012 :  23:39:45  Show Profile  Reply with Quote
Total seconds, like 300 for 5 minutes or 600 for ten.
Also note this is a SQL 2000 server.

Edited by - BobsDesk on 11/06/2012 00:00:15
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 11/05/2012 :  23:56:26  Show Profile  Reply with Quote
hi try this..........

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)



--
Chandu

Edited by - bandi on 11/06/2012 00:10:34
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/06/2012 :  12:07:09  Show Profile  Reply with Quote
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/

Go to Top of Page

BobsDesk
Starting Member

11 Posts

Posted - 11/06/2012 :  16:09:11  Show Profile  Reply with Quote
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/




The difference isn't the issue. Its getting one record per Location, Date, CheckNo and including the item name. For reporting I just multiply the ([End] - Start) times a datetime value of 1 second to display as mm:ss.
I tried Bandi's suggestion for the results aren't right and it took 10 times longer then my current solution which uses a cursor.

Let me clarify a bit more, here is the full actual table structure
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]


Here is some sample data
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


What I currently do works but it uses a cursor and I would like to improve the performace timing, this sproc takes about 7 to 14 minutes depending on the date range depth.

	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

Edited by - BobsDesk on 11/06/2012 18:20:14
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/07/2012 :  09:20:41  Show Profile  Reply with Quote

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



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

Go to Top of Page

BobsDesk
Starting Member

11 Posts

Posted - 11/07/2012 :  10:46:32  Show Profile  Reply with Quote
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/





Robert '); drop table students;-- ?

Edited by - BobsDesk on 11/07/2012 10:47:10
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.11 seconds. Powered By: Snitz Forums 2000