It sounds easy in plain English...!

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)

It sounds easy in plain English...!

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-11-22 : 09:57:31

Please put the following code into Query Analyzer to see what I'm attempting to do...

This code is supposed to search through training events (eventid) and return only the most recent row (max dateAttained) for each event. It fails where 2 events have the same dateAttained and eventid.


declare @tmp table (historyid int, userid int, eventid int, weighting numeric(5,2), dateAttained datetime)
insert into @tmp values (1, 450, 1, 80, '01/01/2001')
insert into @tmp values (2, 450, 2, 20, '01/01/2001 14:56:05')
insert into @tmp values (3, 450, 1, 80, '01/01/2001')
insert into @tmp values (4, 450, 2, 20, '10/01/2001 13:33:25')
insert into @tmp values (5, 450, 2, 20, '01/01/2001')
insert into @tmp values (1, 520, 1, 80, '01/01/2001')
insert into @tmp values (2, 520, 2, 20, '01/01/2001 14:56:05')
insert into @tmp values (3, 520, 1, 80, '01/01/2001')
insert into @tmp values (4, 520, 2, 20, '10/01/2001 13:33:25')
insert into @tmp values (5, 520, 2, 20, '01/01/2001')
select * from @tmp
select *
from @tmp tmp
where
	(userid = 450) and
	(tmp.dateAttained = (select max(dateAttained) from @tmp tmp2 where tmp.eventid = tmp2.eventid))

You will see 2 sets of results. Some of this data is captured by ASP.NET, hence the accurate datetime, and some is manually entered.
The second resultset is supposed to show (for a given user), the most recent training eventid's that they attended, but it doesn't work correctly because 2 (manually entered) events have occurred on the same day. How can I amend the SELECT statement to add the following logic?

"For the given userid, if duplicate eventid/dateAttained rows exist, only show the row with the highest historyid?"

So the correct results would be...:


historyid  eventid	weighting	dateAttained
---------------------------------------------------------------
4	   2		20.00		2001-10-01 13:33:25.000
1	   1		80.00		2001-01-01 00:00:00.000
3	   1		80.00		2001-01-01 00:00:00.000
---------------------------------------------------------------

The second row is eliminated because it has the same eventid/dateAttained as row below it, and also has the lowest historyid of the two.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 10:16:44

[code]select r.*
from @tmp r
inner join (
select t.userid,
t.eventid,
max(t.historyid) mhi
from (
select userid,
eventid,
max(dateattained) mda
from @tmp
group by userid,
eventid
) g
inner join @tmp t on t.userid = g.userid and t.eventid = g.eventid and t.dateattained = g.mda
group by t.userid,
t.eventid
) q on q.userid = r.userid and q.eventid = r.eventid and q.mhi = r.historyid[/code]

Peter Larsson
Helsingborg, Sweden

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-11-22 : 10:36:36

Hi Peter

Thanks for your quick reply! Let me have a look at your logic and apply it into my application...

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-11-22 : 10:50:27

Wow! It works great. That is very impressive...!

Thank you so much for your help, Peter.

I'm going to spend a little time following your logic and learning how you achieved this.

T-SQL seemed so easy in the training videos...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources