New records this month per group CR 8.5 SQL Db

[risman]

I'm trying to display a patient's exam if in the past month, it is the first one ever. I have witten a report that gathers our whole Db. It is grouped by Patient ID, sorted by Date of Service, (descending), and I have a running total of the exams that restarts with each Patient ID group. Then I suppress any record that does not have a running total of "1". So I have every patient's very first exam. The problem is it could be from 1996 for example. If I try to suppress any record that is not done in the past month or year and does not have the running total of 1, I get all kinds of records. Any insight would be great! Thank you.

 

[lbass]

You would have to be using an ascending sort, and then make your suppression criterion be:

{#rt} > 1 or
{table.date} < minimum(lastfullmonth)

A better way to do it would be to go to report->selection formula->GROUP and enter:

{table.date} = minimum({table.date},{table.patient}) and
{table.date} in lastfullmonth

This would return only those patients whose first exam was within the last full month. Records from all dates would still be in the report, even though not displayed, and so you would have to use running totals for any calculations across patients.

-LB

 

[risman]

LB............You ROCK!! I've never used minimum / maximum in formulas and queries before. Thank you very much!
Ris...