Re: Help w/ this Query...
This solution may be more complicated than you need, but I hope it gets
you on the right track. Simply changing "unique(case_no)" to "case_no"
might also work and would be much easier. Without seeing the results
your query generates, it is hard to know exactly what the problem is.
Good luck.
CREATE VIEW mycase ASSELECT
case_no,
max (dis_date) as maxdate,
coll_agency
FROM case_dispos
WHERE (dis_date between "01/01/88" AND today)
AND disposition = "RF"
AND authr = "CA"
group by case_no, coll_agency;
SELECT
a.case_no,
a.dis_date,
a.coll_agency
FROM case_dispos a, mycase b
WHERE (a.dis_date between "01/01/88" AND today)
AND a.disposition = "RF"
AND a.authr = "CA"
AND a.case_no = b.case_no AND a.coll_agency = b.coll_agency AND
a.dis_date = b.maxdate
group by case_no, coll_agency
order by coll_agency;
Hannah Sobie Fiabema wrote:
>
> Gurus,
> I have data that looks like this:
>
>
> case # date
> ------- ----
>
> 913-01234 01/01/95
> 913-01234 02/02/95
> 913-01234 03/03/96
>
> . .
> . .
> . .
>
> I am writing a report and for each instance of the case number I
> just need to retrieve the LAST case w/ the most recent date. In this
> example, I WOULD ONLY WANT TO PRINT - 913-01234 03/03/96, ok.
>
> This is my problem query:
>
> SELECT
> unique(case_no),
> max (dis_date),
> coll_agency
> FROM case_dispos
> WHERE (dis_date between "01/01/88" AND today)
> AND disposition = "RF"
> AND authr = "CA"
> group by case_no, coll_agency
> order by coll_agency
>
> Thnks in advance.