comparing MAX dates from same table

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

Transact-SQL (2000)

comparing MAX dates from same table

Author

Topic

michaeld
Starting Member

8 Posts

Posted - 2004-03-05 : 13:08:33

I would like to compare the notes table for MAX addDate for each user for a particular noteid. I would like notes to be selected if problem.startDate-getdate() is > 3 days and (MAX(bob addDate)-MAX(mike)) > 3 days. Then join the problem table an show additional info.

I would like to see:
problemid user Rep startdate last_AddDate added_by
215 bob mike 2003-06-23 2003-06-29 bob

Problems
--------
ProblemID Title StartDate status User Rep
214 Outlook 2002-05-31 closed bob mike
215 Word 2003-06-23 Open bob mike

Notes
-----
NoteID Note AddDate User
214 I need help 2002-05-31 bob
214 Transfer to mike 2002-06-01 mike
214 fixed:Config issue 2002-06-01 mike
215 Word not starting 2003-06-23 bob
215 Update on issue pls 2003-06-29 bob

Users
-----
userid Name isRep
34 bob 0
45 mike 1

If any one has suggestion it would help! Thanks

ChrisFretwell
Starting Member

43 Posts

Posted - 2004-03-05 : 14:05:05

Well, if possible, I'd sugges changing the user/rep fields in the notes and problem tables to use the userid rather than the name. Its much safer (more than one person with same name, spelling issue etc), not to mention a best practice technique. Also, your noteid fields is erally the problemid field (at least in this small example) If your notes date is just a date, and there is no other field to indicate order added, then you will have a problem finding out which ones is really the most recent. I put a time value in for the June 1 records.

But that aside, here goes (uses temp tables - with no need for the user table)

create table #Problems
(problemid int,
title varchar(30),
startdate datetime,
status varchar(10),
username varchar(10),
rep varchar(10))

drop table #notes
create table #notes
(noteid int,
note varchar(180),
adddate datetime,
username varchar(10))

insert #problems values(214, 'Outlook', '2002-05-31', 'closed', 'bob', 'mike')
insert #problems values(215, 'Word', '2003-06-23', 'Open', 'bob' ,'mike')
insert #notes values('214', 'I need help', '2002-05-31', 'bob')
insert #notes values('214', 'Transfer to mike', '2002-06-01 12:00' ,'mike')
insert #notes values('214' ,'fixed:Config issue', '2002-06-01 15:00','mike')
insert #notes values('215', 'Word not starting', '2003-06-23', 'bob')
insert #notes values('215', 'Update on issue pls', '2003-06-29', 'bob')

select p.problemid, p.username, p.rep,startdate, n1.adddate, n1.username
from #problems p
join #notes n1 on p.problemid = n1.noteid
where n1.adddate = (select max(adddate) from #notes n2 where n1.noteid = n2.noteid)
and datediff(day,p.startdate,getdate()) > 3
and datediff(day,p.startdate,n1.adddate)> 3

michaeld
Starting Member

8 Posts

Posted - 2004-03-05 : 14:52:56

This looks great! Is there any way to compare the max(adddate) from the user and the rep and display one row if the USER [max(adddate)] - REP [max(adddate)] > 3 ?

OK. for me to understandable also. (This is confusing)

senario 1
p.startdate - getdate() > 3 (show one row for problem)
AND
(NO REP n.adddate or IF REP note exists, p.startdate - oldest REP n.adddate > 3)
senario 2
p.startdate - getdate() > 3
AND
(max USER n.adddate - max REP n.adddate > 3)

Hopefully this makes sense!! If you have any more suggestion, thanks in advance.

quote:
Originally posted by ChrisFretwell

Well, if possible, I'd sugges changing the user/rep fields in the notes and problem tables to use the userid rather than the name. Its much safer (more than one person with same name, spelling issue etc), not to mention a best practice technique. Also, your noteid fields is erally the problemid field (at least in this small example) If your notes date is just a date, and there is no other field to indicate order added, then you will have a problem finding out which ones is really the most recent. I put a time value in for the June 1 records.

But that aside, here goes (uses temp tables - with no need for the user table)

create table #Problems
(problemid int,
title varchar(30),
startdate datetime,
status varchar(10),
username varchar(10),
rep varchar(10))

drop table #notes
create table #notes
(noteid int,
note varchar(180),
adddate datetime,
username varchar(10))

insert #problems values(214, 'Outlook', '2002-05-31', 'closed', 'bob', 'mike')
insert #problems values(215, 'Word', '2003-06-23', 'Open', 'bob' ,'mike')
insert #notes values('214', 'I need help', '2002-05-31', 'bob')
insert #notes values('214', 'Transfer to mike', '2002-06-01 12:00' ,'mike')
insert #notes values('214' ,'fixed:Config issue', '2002-06-01 15:00','mike')
insert #notes values('215', 'Word not starting', '2003-06-23', 'bob')
insert #notes values('215', 'Update on issue pls', '2003-06-29', 'bob')

select p.problemid, p.username, p.rep,startdate, n1.adddate, n1.username
from #problems p
join #notes n1 on p.problemid = n1.noteid
where n1.adddate = (select max(adddate) from #notes n2 where n1.noteid = n2.noteid)
and datediff(day,p.startdate,getdate()) > 3
and datediff(day,p.startdate,n1.adddate)> 3

ChrisFretwell
Starting Member

43 Posts

Posted - 2004-03-05 : 16:20:56

If I understand then, you want where there is more than 3 days between last note from the rep and the last note from the user, or if there is no note from the rep?
Yes you can do it. I need to think about it a bit though.
I take it this is like a HD application. Can a user be a rep on the same call?

michaeld
Starting Member

8 Posts

Posted - 2004-03-05 : 16:38:20

Yes almost.

where there is more than 3 days between last note from the USER and the last note from the REP, or if there is no note from the rep.

We use 'Liberum Help Desk'.

michaeld
Starting Member

8 Posts

Posted - 2004-03-08 : 12:19:05

This is what i have so far. Any suggestion would be great. I am having problems with the last part of my having statment. I made part of the having statement = 1 just so I could test to see if it would return a row.

------------------------
select n.noteid, n.username,max(n.adddate) -- max(adddate)
from #notes n
JOIN #problems p
ON n.noteid = p.problemid
where n.noteid = p.problemid and
n.adddate = (select max(adddate)
from #notes n1
where n1.username = p.username and n1.noteid = p.problemid
--or
--n1.username = p.rep and n1.noteid = p.problemid
)
group by n.noteid,n.username, n.adddate, p.username, p.problemid,p.rep

having datediff(day,
(select max(adddate)
from #notes n1
where n1.username = p.username and n1.noteid = p.problemid)
,
(select max(adddate)
from #notes n1
where n1.username = p.rep and n1.noteid = p.problemid)
) = 1
or
( datediff(day,
(select max(adddate)
from #notes n1
where n1.username = p.username and n1.noteid = p.problemid)
,
getdate()
) > 3
AND
n.AddDate <> (select max(adddate)
from #notes n1
where n1.username = p.rep
and n1.noteid = p.problemid )
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources