Get MAX dates from a subset of a recordset

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 2000 Forums

SQL Server Development (2000)

Get MAX dates from a subset of a recordset

Author

Topic

jaroot
Starting Member

46 Posts

Posted - 2005-06-03 : 15:47:09

[code]
ID USER DATE
48 12090 2005-04-26 14:09:00
48 12090 2005-04-27 08:21:00
97 12090 2005-05-01 10:34:00
97 12090 2005-05-02 11:47:00
97 12095 2005-05-03 10:50:00
97 12095 2005-05-08 11:53:00
99 12090 2005-05-01 10:47:00
100 12090 2005-05-01 10:48:00
100 12090 2005-05-01 11:48:00
100 12095 2005-05-01 11:49:00
100 12095 2005-05-02 10:49:00
101 12090 2005-05-01 09:48:00
101 12095 2005-05-06 11:22:00
104 12090 2005-05-11 12:34:00
104 12095 2005-05-30 12:34:00
[/code]

I know this is a pretty simple one, but I'm not sure how to do it.

From this recordset, I need write a query to get all the most recent date for every ID

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-03 : 15:55:42

Why not give it a try and let us correct your query if necessary?

You said all the right words... just put it into a query now :)

jaroot
Starting Member

46 Posts

Posted - 2005-06-03 : 15:57:38

I also need the AVG response time by a given user.

So for user 12095.. I would need the sum of the avg times between...
97 12090 2005-05-02 11:47:00
97 12095 2005-05-03 10:50:00
&
100 12090 2005-05-01 11:48:00
100 12095 2005-05-01 11:49:00
&
101 12090 2005-05-01 09:48:00
101 12095 2005-05-06 11:22:00
&
104 12090 2005-05-11 12:34:00
104 12095 2005-05-30 12:34:00

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-03 : 15:59:07

I'm outta here....but that was good advice...try it first....and read the link below


USE Northwind 
GO

SET NOCOUNT ON
CREATE TABLE myTable99([ID] int, [USER] int, [DATE] datetime)
GO

INSERT INTO myTable99([ID], [USER], [DATE])
SELECT  48,	12090,	'2005-04-26 14:09:00' UNION ALL
SELECT  48,	12090,	'2005-04-27 08:21:00' UNION ALL
SELECT  97,	12090,	'2005-05-01 10:34:00' UNION ALL
SELECT  97,	12090,	'2005-05-07 11:47:00' UNION ALL
SELECT  97,	12095,	'2005-05-03 10:50:00' UNION ALL
SELECT  97,	12095,	'2005-05-08 11:53:00' UNION ALL
SELECT  99,	12090,	'2005-05-01 10:47:00' UNION ALL
SELECT  100,	12090,	'2005-05-01 10:48:00' UNION ALL
SELECT  100,	12090,	'2005-05-01 11:48:00' UNION ALL
SELECT  100,	12095,	'2005-05-01 11:47:00' UNION ALL
SELECT  100,	12095,	'2005-05-02 10:49:00' UNION ALL
SELECT  101,	12095,	'2005-05-01 11:25:00' UNION ALL
SELECT  101,	12095,	'2005-05-06 11:22:00' UNION ALL
SELECT  101,	12113,	'2005-05-01 11:01:00' UNION ALL
SELECT  104,	12090,	'2005-05-11 12:34:00' UNION ALL
SELECT  104,	12095,	'2005-05-30 12:34:00'
GO


SELECT * 
  FROM myTable99 o 
 WHERE o.[DATE] = (SELECT MAX(i.[DATE])
		     FROM myTable99 i 
		    WHERE i.[ID] = o.[ID])
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

jaroot
Starting Member

46 Posts

Posted - 2005-06-03 : 16:01:07

quote:
Originally posted by nathans

Why not give it a try and let us correct your query if necessary?

You said all the right words... just put it into a query now :)

heh.. that's just it, I can visualize it, I'm just not good enough w/ the T-SQL syntax yet to put it into query form.. I'm trying though.

Here's my feeble attempts so far:
Get dates that are 3 days old.. but ONLY the MAX date from each subset..
SELECT ID, UserID, MAX(Date) AS maxdate
FROM tComment
WHERE datediff(d, Date, getDate()) > 3
GROUP BY ID, UserID, Date
ORDER BY ID

This just brings back all 3 day old dates though...

jaroot
Starting Member

46 Posts

Posted - 2005-06-03 : 16:07:18

quote:

Originally posted by X002548


USE Northwind 
GO

SET NOCOUNT ON
CREATE TABLE myTable99([ID] int, [USER] int, [DATE] datetime)
GO

INSERT INTO myTable99([ID], [USER], [DATE])
SELECT  48,	12090,	'2005-04-26 14:09:00' UNION ALL
SELECT  48,	12090,	'2005-04-27 08:21:00' UNION ALL
SELECT  97,	12090,	'2005-05-01 10:34:00' UNION ALL
SELECT  97,	12090,	'2005-05-07 11:47:00' UNION ALL
SELECT  97,	12095,	'2005-05-03 10:50:00' UNION ALL
SELECT  97,	12095,	'2005-05-08 11:53:00' UNION ALL
SELECT  99,	12090,	'2005-05-01 10:47:00' UNION ALL
SELECT  100,	12090,	'2005-05-01 10:48:00' UNION ALL
SELECT  100,	12090,	'2005-05-01 11:48:00' UNION ALL
SELECT  100,	12095,	'2005-05-01 11:47:00' UNION ALL
SELECT  100,	12095,	'2005-05-02 10:49:00' UNION ALL
SELECT  101,	12095,	'2005-05-01 11:25:00' UNION ALL
SELECT  101,	12095,	'2005-05-06 11:22:00' UNION ALL
SELECT  101,	12113,	'2005-05-01 11:01:00' UNION ALL
SELECT  104,	12090,	'2005-05-11 12:34:00' UNION ALL
SELECT  104,	12095,	'2005-05-30 12:34:00'
GO


SELECT * 
  FROM myTable99 o 
 WHERE o.[DATE] = (SELECT MAX(i.[DATE])
		     FROM myTable99 i 
		    WHERE i.[ID] = o.[ID])
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Cool.. this works. How bout AVG response time from any user given the user ID?

I'd post what I have for that too so far.. but it's not even close I'm sure.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-03 : 16:19:38

what defines responseTime?

Please post desired resultset.

jaroot
Starting Member

46 Posts

Posted - 2005-06-03 : 16:25:18

quote:
Originally posted by nathans

what defines responseTime?

Please post desired resultset.

I just need the time (in hours or days) it takes for the 2nd user
in any given thread(ID) to respond to the first users comment.

So in the records I posted below...
97 12090 2005-05-02 11:47:00
97 12095 2005-05-03 10:50:00
(23 hours) or 1 day
&
100 12090 2005-05-01 11:48:00
100 12095 2005-05-01 11:49:00
(1 minute) <-- we can exlude this
&
101 12090 2005-05-01 09:48:00
101 12095 2005-05-06 11:22:00
(4 days)
&
104 12090 2005-05-11 12:34:00
104 12095 2005-05-30 12:34:00
(19 days)

So 19 + 4 + 1 + 0 (the one minute record that we can exlude)= 24

I need the AVG from the 3 sequeneces of ID's.. so 24/4 = 6 day avg

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-03 : 19:26:13

okay, I had some trouble with this one. Im hoping someone steps in a shows us both a better way, but here is my first crack.

Basically, I tried to isolate the opening request, the first response to that request, and the elasped time between the two building on Brett's query.

edit: oops, forgot to return the avg

declare @tblGrievanceComment table([intGrievanceID] int, [intUserID] int, [sdtCommentDate] datetime)
insert into @tblGrievanceComment
select	48,	12090,	'2005-04-27 14:10:00' union
select	48,	12090,	'2005-04-27 14:11:00' union
select	97,	12090,	'2005-05-01 10:34:00' union
select	97,	12090,	'2005-05-02 11:35:00' union
select	97,	12095,	'2005-05-03 10:50:00' union
select	97,	12095,	'2005-05-08 11:53:00' union
select	99,	12090,	'2005-05-01 10:47:00' union
select	100,	12090,	'2005-05-01 10:48:00' union
select	100,	12090,	'2005-05-01 10:48:00' union
select	100,	12095,	'2005-05-01 11:49:00' union
select	100,	12095,	'2005-05-02 10:49:00' union
select	101,	12090,	'2005-05-11 09:48:00' union
select	101,	12095,	'2005-05-30 11:22:00' union
select	104,	12090,	'2005-05-11 12:34:00' union
select	104,	12095,	'2005-05-30 12:34:00'


select	cast(cast(avg(datediff(ss,d.grievanceOpen ,a.sdtCommentDate))/(3600*24) as int) as varchar) + 'd ' +
	cast(cast(avg(datediff(ss,d.grievanceOpen ,a.sdtCommentDate))%(3600*24) as int)/3600 as varchar) + 'h ' +
        cast(cast(avg(datediff(ss,d.grievanceOpen ,a.sdtCommentDate))%3600 as int)/60 as varchar) + 'm ' +
	cast(cast(avg(datediff(ss,d.grievanceOpen ,a.sdtCommentDate))%60 as int) as varchar) + 's '
from @tblGrievanceComment a 
inner join (	select	aa.intUserID,
			aa.intGrievanceID,
			aa.sdtCommentDate as 'grievanceOpen'
		from @tblGrievanceComment aa
		where aa.sdtCommentDate = (	select min(i.sdtCommentDate) 
						from @tblGrievanceComment i 
						where i.intGrievanceID = aa.intGrievanceID)) d
on a.intGrievanceID = d.intGrievanceID
where a.sdtCommentDate = (	select top 1 ii.sdtCommentDate 
				from @tblGrievanceComment ii 
				where ii.intGrievanceID = d.intGrievanceID 
					and ii.sdtCommentDate >= d.grievanceOpen 
				order by 1 desc)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-04 : 00:58:41

here's my go...


declare @tblGrievanceComment table([intGrievanceID] int, [intUserID] int, [sdtCommentDate] datetime)
insert into @tblGrievanceComment
select	48,	12090,	'2005-04-27 14:10:00' union
select	48,	12090,	'2005-04-27 14:11:00' union
select	97,	12090,	'2005-05-01 10:34:00' union
select	97,	12090,	'2005-05-02 11:35:00' union
select	97,	12095,	'2005-05-03 10:50:00' union
select	97,	12095,	'2005-05-08 11:53:00' union
select	99,	12090,	'2005-05-01 10:47:00' union
select	100,	12090,	'2005-05-01 10:48:00' union
select	100,	12090,	'2005-05-01 10:48:00' union
select	100,	12095,	'2005-05-01 11:49:00' union
select	100,	12095,	'2005-05-02 10:49:00' union
select	101,	12090,	'2005-05-11 09:48:00' union
select	101,	12095,	'2005-05-30 11:22:00' union
select	104,	12090,	'2005-05-11 12:34:00' union
select	104,	12095,	'2005-05-30 12:34:00'


Select
	intUserId,
	AvgResponse = convert(varchar,AvgResponse/(3600*24)) + 'days ' +
			convert(varchar,(AvgResponse%(3600*24))/3600) + 'hrs ' +
			convert(varchar,(AvgResponse%3600)/60) + 'min ' +
			convert(varchar,AvgResponse%60) + 'sec '-- +
From
	(
	Select 
		intUserId, 
		AvgResponse = avg(datediff(ss,respondingTo,sdtCommentDate))
	From
		(
		Select 
			intUserId, 
			sdtCommentDate, 
			respondingTo = (select max(sdtCommentDate) From @tblGrievanceComment Where intGrievanceId = A.intGrievanceId and intUserId <> A.intUserId and sdtCommentDate < A.sdtCommentDate)
		From @tblGrievanceComment A
		) Z
	Where respondingTo is not null
	Group By intUserId
	) A

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-14 : 22:22:15

An explanation was requested regarding my solution, so here it goes:

Referencing the color coded query from inside out:
green: The green section is the source of the recordset, aka every record in the source table

blue: The blue section identifies the greatest time (sdtCommentDate) that is related
to the current grievance (intGrievanceId) but is not for the same user (intUserId)
where the sdtCommentDate is less than sdtCommentDate

orange: The orange section filters the source results to only those that had a RespondingTo entry.
This means, that the current record was a response to a grievance entry (where 'response' is defined
as regarding the same grievance, but by a different user.) This section also uses datadiff to determine
the response interval in seconds. This value is then averaged with respect to each user (by
specifying: Group By intUserId)

red: The red section is solely for display purposes, the days, hours, minutes, and seconds are
identified by using '/' & '%', otherwise known as 'divide' and 'modulo' (remainder).


Select
	intUserId,
	AvgResponse = convert(varchar,AvgResponse/(3600*24)) + 'days ' +
			convert(varchar,(AvgResponse%(3600*24))/3600) + 'hrs ' +
			convert(varchar,(AvgResponse%3600)/60) + 'min ' +
			convert(varchar,AvgResponse%60) + 'sec 
From
	(
	Select 
		intUserId, 
		AvgResponse = avg(datediff(ss,respondingTo,sdtCommentDate))
	From
		(
		Select 
			intUserId, 
			sdtCommentDate, 
			respondingTo = (select max(sdtCommentDate) From @tblGrievanceComment Where intGrievanceId = A.intGrievanceId and intUserId <> A.intUserId and sdtCommentDate < A.sdtCommentDate)
		From @tblGrievanceComment A
		) Z
	Where respondingTo is not null
	Group By intUserId
	) A

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources