Most recent record

[nicatt]

I have a table which may have multiple records for each person.

Roughly speaking the table has:
name code start_date end_date class
Bob 1234 20000101 20001231 P
Bob 1234 20010101 20011231 M
Bob 1234 20020101 20020604 C
Bob 1234 20020604 99991231 M*
Sue 2234 20000101 20001231 P
Sue 2234 20010101 20010331 C
Sue 2234 20020401 20020604 M*
Sue 2234 20020605 20030202 U
Sue 2234 20030203 99991231 M

I want the last row for each person where the class is 'M' and the start_day is less than today.

So I essentially want the MAX(record) for each person where class = 'M' and start_date <= run_date. The ones noted above with an astrick by the 'M' would be the ones I want.

Ideas?

 

[nbrk]

You could try something like this....it may need some tweaking

select name, code, start_date, end_date, class
from test_table a
where class = 'M'
and convert(smalldatetime, start_date) < getdate()
and not exists
(select 1
from test_table b
where a.name = b.name
and a.code = b.code
and b.class =a.class
and convert(smalldatetime, b.start_date) > convert(smalldatetime, a.start_date)
and convert(smalldatetime, b.start_date) < getdate()
)


Good Luck

 

[JMCraig]

Hi.

It's not entirely clear what the SQL definition of MAX(record) is, but in the case of the examples you have in your sample, it appears to be MAX (start_date). The data type of the start_date column's also unclear (is it characters or a numeric?).

Assuming that's the correct interpretation, this should work:
[tt]
select * /* or whatever you need */
from <your table> t
where class = 'M' /* the easy part */
and start_date =
(select max (start_date) from <your table>
where name = t.name /* unclear if needed */
and code = t.code
and class = t.class
and start_date <
< convert (/* correct data type */,
convert (char (8), getdate (), 112)
/* today in yyyymmdd format */
) /* outer convert
not needed if start_date
is a char type */
)
[/tt]

The subquery's job is to select the maximum start_date value for the student in question where the start_date is less than today. One problem you could encounter is that the subquery may return multiple, identical rows. Presumably this circumstance either wouldn't arise because of some reason beyond the data itself, or you'd have to use an IN operator before the subquery rather than the = operator--since the MAX function is only going to return a single value, the use of IN (which de-dupes its list) would be functionally equivalent to = even if there are duplicate start_date entries for a given student in 'M'.

Of course, this whole mess assumes that the tabular definition of MAX(record) is the max start_date. If not, then well, I guess we need more info.

Best of luck,

John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[JMCraig]

BTW, if your table's large, then the subquery approach is going to likely cause an expensive table scan (you can use SHOWPLAN ON / NOEXEC ON to check this before running it).

In that case, I'd recommend something like this:

select name, code, start_date = max (start_date)
into tempdb..max_start_dates
from <your table>
where class = 'M'
group by name, code

Then you can report on things with this join pattern:

select d.*
from <your table> d, tempdb..max_start_dates t
where d.name = t.name /* necessary? */
and d.code = t.code
and d.class = 'M'
and d.start_date = t.start_date

HTH,

John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[nicatt]

r937 (TechnicalUser) Nov 24, 2002
Workable answer from SQL Server threads

select name, code
, start_date, end_date
from yourtable xx
where start_date =
( select max(start_date)
from yourtable
where name = xx.name
and class = 'M'
and start_date <= getdate() )

rudy