Distinct Max Value with Details

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

Distinct Max Value with Details

Author

Topic

BobsDesk
Starting Member

11 Posts

Posted - 2012-11-05 : 17:46:21

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

52326 Posts

Posted - 2012-11-05 : 20:58:56

How are values currently in Start and End fields? its integer field so what does values represent?

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

BobsDesk
Starting Member

11 Posts

Posted - 2012-11-05 : 23:39:45

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-05 : 23:56:26

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-06 : 12:07:09

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/

BobsDesk
Starting Member

11 Posts

Posted - 2012-11-06 : 16:09:11

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-07 : 09:20:41

[code]
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
[/code]

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

BobsDesk
Starting Member

11 Posts

Posted - 2012-11-07 : 10:46:32

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;-- ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources