Distinct ID and MIN Date Return

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

SQL Server 2008 Forums

Transact-SQL (2008)

Distinct ID and MIN Date Return

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-05-25 : 03:20:00

Hi,

I am trying to return only the first record which will be the first time field which I've used MIN(UpdatedTime) AS RespondTime. Basically the RequestID is the same number which can be duplicated several times a call comes in but the WrapUpCode will be different depending on what happens to the call, i.e. there can be many "0", "1", "2"...I've put Distinct to only show one record of the RequestID and the MIN function to return me the first time field record but looking at the results I get all RequestIDs and WrapUpCodes that = 0. I must be missing something silly here...help appreciated.

USE [tm2000]
GO

/****** Object: View [dbo].[FirstResponse] Script Date: 05/25/2011 08:12:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[FirstResponse]
AS
SELECT DISTINCT
RequestID,
RequestTime,
MIN(UpdatedTime) As RespondTime,
CustomerName,
AllocatedAgentName,
WrapUpCode
FROM WebRequestsLog
WHERE WrapUpCode =0

GROUP BY RequestID, RequestTime, UpdatedTime, CustomerName, AllocatedAgentName, WrapUpCode

GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-25 : 03:22:30

remove UpdatedTime from the GROUP BY

KH
[spoiler]Time is always against us[/spoiler]

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-05-25 : 03:38:23

Removed UpdatedTime but results still same with every RequestID showing and WrapUpCode =0.

USE [bcs2000]
GO

/****** Object: View [dbo].[FirstResponse] Script Date: 05/25/2011 08:12:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[FirstResponse]
AS
SELECT DISTINCT
RequestID,
RequestTime,
MIN(UpdatedTime) As RespondTime,
CustomerName,
AllocatedAgentName,
WrapUpCode
FROM WebRequestsLog
WHERE WrapUpCode =0

GROUP BY RequestID, RequestTime, CustomerName, AllocatedAgentName, WrapUpCode

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-05-25 : 03:42:15

;with cte
as (select *, row_number() over (partition by requestid order by updatedtime) as seqid from webrequestlog where wrapupcode = 0)
select * from cte where seqid = 1

N 56°04'39.26"
E 12°55'05.63"

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-05-25 : 03:46:44

Do you mean use this instead of the code I have or append this to the query?
can you advice?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources