Table join with MAX value... - SQL Server Forums

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)

Table join with MAX value...

Author

Topic

aecojohnc
Starting Member

2 Posts

Posted - 2003-01-30 : 19:31:47

Fixing some broken code here and my brain hurts...

Have two tables, one with policy data (Policy) and one with activites (Notes) linked by PolicyID. I am wanting to display a list of policies with the latest activity date.

(MSSQL 8.00.534(SP2))

here is the statement in the stored procedure that should give me that list:
-------
select p.PolicyID, p.PolicyNo, LifeSurname, LifeGivenName, p.CreateDate, max(n.FollowUpDate) as followupdate, PolicyDesc, EmployerSuperfund, p.status
from Policy p left outer join Notes n on p.PolicyID = n.PolicyID
where p.PolicyID = n.PolicyID and n.FollowupDate < getdate()and p.status = 1
group by p.PolicyID, p.PolicyNo, LifeSurname, LifeGivenName, p.CreateDate, PolicyDesc, EmployerSuperfund, p.status
order by followupdate, LifeSurname
--------

Suffice to say it doesn't always do it... (not all the time or reliably) problem is obviously the max(nFollowUpDate) but not sure why

I have another statement that simply gets the latest date for an individual policy and works every time.

--------
select max(followupDate) as followupDate from notes where policyid = @policyID
--------

help - should be simple

Tigger
Yak Posting Veteran

85 Posts

Posted - 2003-01-30 : 19:50:55

Try

select
p.PolicyID, p.PolicyNo, LifeSurname, LifeGivenName, p.CreateDate,
max(n.FollowUpDate) as followupdate, PolicyDesc,
EmployerSuperfund, p.status
from
Policy p
left outer join
(select policyid, max(followupDate) as followupDate from notes
where followupdate < getdate() group by policyid) n
on
p.PolicyID = n.PolicyID
where
p.status = 1
group by
p.PolicyID, p.PolicyNo, LifeSurname, LifeGivenName, p.CreateDate,
PolicyDesc, EmployerSuperfund, p.status
order by
followupdate, LifeSurname

I believe with your original statement by referencing table n in the where clause in effect changes the left outer join to an inner join. Not sure if that is the cause of your problems though.

bm1000
Starting Member

37 Posts

Posted - 2003-01-30 : 19:55:13

select * from policy p, notes n
where p.policyid = n.policyid
and n.followupdate = (
select max(followupdate) from notes
where policyid = n.policyid
and followupdate < getdate()
)
order by ...

by the way, your sql server version number suggests you need the sqlslammer hotfix.

aecojohnc
Starting Member

2 Posts

Posted - 2003-01-30 : 20:02:11

thanks guys - in the interim I came up with this one - which works.

Not sure about efficiency but it deffinately does the job properly:

-----
select p.PolicyID, p.PolicyNo, LifeSurname, LifeGivenName, p.CreateDate,
(select max(FollowUpDate) from Notes n2 where p.PolicyID = n2.PolicyID) as followupdate,
PolicyDesc, EmployerSuperfund, p.status
from Policy p, Notes n
where p.PolicyID = n.PolicyID and n.FollowUpDate < getdate() and p.status = 1
group by p.PolicyID, p.PolicyNo, LifeSurname, LifeGivenName, p.CreateDate, PolicyDesc, EmployerSuperfund, p.status
order by p.policyID, followupdate, LifeSurname
-----

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources