max date with specific criteria off 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

General SQL Server Forums

New to SQL Server Programming

max date with specific criteria off same table

Author

Topic

3OfHarts
Starting Member

7 Posts

Posted - 2009-09-22 : 17:27:56

I am having a problem selecting records whose status code = 'N' only if it is the record with the lastest date. In other words if I have multiple records for the same person that looks like this:

ID STATUS_CODE ACTIVITY_DATE
1234 I 14-AUG-2009
1234 N 1-AUG-2009

I wouldn't want this person on my list because the latest date doesn't have a status of N. Instead I am getting everyone with an N regardless of whether or not it's the lastest record. I have tried several different scenarios with no luck. This is what I have that isn't working. I have this embedded in with other joins and selection criteria, but this is the part that doesn't seem to be working. I am still fairly new to SQL, so don't be too hard on me.

select * from (select tzrstsf_pidm,tzrstsf_activity_date, tzrstsf_status_code, tzrstsf_term
from tzrstsf,
(select max(z.tzrstsf_activity_date)as maxdate from tzrstsf z group by z.tzrstsf_pidm) maxresults
where tzrstsf_term = '200980' and tzrstsf_activity_date = maxresults.maxdate)
where tzrstsf_status_code = 'N';

Michelle Harts
Volunteer State Comm Coll
Gallatin, Tn

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-22 : 17:34:35

Here's one way:


select yt.id
       ,yt.status_code
       ,yt.activity_date
from   (
       select id
              ,'N' as status_code
              ,max(activity_date) maxDt
       from   [yourTable]
       where  status_code = 'N'
       group by id
       ) as d
inner join [yourTable] yt
       on yt.id = d.id
       and yt.activity_date = d.maxDt
       and yt.status_code = d.status_code

And here's another assuming you are using 2005 or later:


select id
       ,min(ca.status_code) as status_Code
       ,min(ca.activity_date) as activity_date
from   [yourTable] yt
cross apply (
       select top 1 status_code, activity_date
       from   [yourTable]
       where  id = yt.id
       and    status_code = yt.status_code
       order by activity_date desc
       ) as ca
where  status_code = 'N'
group by id

Be One with the Optimizer
TG

3OfHarts
Starting Member

7 Posts

Posted - 2009-09-23 : 10:51:49

When I attempt to run the first example it gives me an error, SQL command not properly ended, on this line: )as d

Michelle Harts
Volunteer State Comm Coll
Gallatin, Tn

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-23 : 11:04:27

How are you running the statement? Did you paste the entire statement into a MS Sql Server query window and execute it from there?

Be One with the Optimizer
TG

3OfHarts
Starting Member

7 Posts

Posted - 2009-09-23 : 11:12:21

This is how I changed it to pull records from my table. I ran this from SQL Developer and SQLPLUS on the oracle server. Is that why I am having a problem?

select a.tzrstsf_pidm, a.tzrstsf_status_code, a.tzrstsf_activity_date
from (select tzrstsf_pidm, 'N' as tzrstsf_status_code, max(tzrstsf_activity_date) maxdt
from tzrstsf
where tzrstsf_status_code = 'N'
and tzrstsf_term = '200980'
group by tzrstsf_pidm
)as d
inner join tzrstsf a
on a.tzrstsf_pidm = d.tzrstsf_pidm
and a.tzrstsf_activity_date = d.maxdt
and a.tzrstsf_status_code = d.tzrstsf_status_code;

Thanks so much for your help.

Michelle Harts
Volunteer State Comm Coll
Gallatin, Tn

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-23 : 11:17:19

Oh I see. This site is specific to MS Sql Server. In the future, for Oracle questions, try dbforums.com.

Be One with the Optimizer
TG

3OfHarts
Starting Member

7 Posts

Posted - 2009-09-23 : 11:19:50

Oh okay. Thank you.

Michelle Harts
Volunteer State Comm Coll
Gallatin, Tn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources