Max date with other criteria 1

[RenaG]

Using Crystal 10.

I need to select patients whose last appointment date > 4/30/2011 and the appointment is not a NOTE and the appointment wasn't canceled.

In looking in the forum I found a SQL statement that addresses the last appointment date part. I tried adding code for the rest of the criteria and came up with:

(
select max("App_DtTm")
from Schedule A
where A."Pat_ID1"="Schedule"."Pat_ID1"  and
"Schedule"."Activity" <> 'NONE' and
("Schedule"."SchStatus_Hist_SD" <> 'N' or
 "Schedule"."SchStatus_Hist_SD" <> 'X') and
("Schedule"."App_DtTm" >= {fn MONTH(05)} and
"Schedule"."App_DtTm" >= {fn YEAR(2011)})
)

In the Record Selection:
{Schedule.App_DtTm} = {%LastApptDt}

But it's not working. I am getting appointments that are NOTES and also getting appointment that are before 4/30/2011.

Is this the best way to select these records? If so, how do I fix this statement? If not, what other method would work better?

TIA!
~RLG

 

[briangriffin]

First thing I notice is this:

("Schedule"."SchStatus_Hist_SD" <> 'N' or
"Schedule"."SchStatus_Hist_SD" <> 'X')

This would always evaluate to true - every code, even N and X, is not one of these. This will exclude N's and X's:

"Schedule"."SchStatus_Hist_SD" not in ('N','X')

Since that was the only OR in the statement, you don't have to use parentheses other than to wrap the subquery:

(
select max("App_DtTm")
from Schedule A
where A."Pat_ID1"="Schedule"."Pat_ID1" and
"Schedule"."Activity" <> 'NONE' and
"Schedule"."SchStatus_Hist_SD" not in ('N','X') and
"Schedule"."App_DtTm" >= '5/1/2011
)

Also, you can try both ways but changing the date logic will probably run faster since you're not making two function calls for every record.

 

[RenaG]

Hi Brian,

Thanks for your quick reply. I can't believe I fell for that Not = / Or logic. I know better than that! I had copied from the Record Selection the "not ({Schedule.SchStatus_Hist_SD} in ["N", "X"])" which of course doesn't work in SQL and didn't know how to write it correctly.

I also realized that I had put "NONE" in the criteria instead of "NOTE" which explains why I was getting NOTE appointments.

Thanks for fixing the date check. Since it wasn't working, I had decided I could pull that part out of the SQL and put it in the Record Selection (which does appear to work) but I think having it in the SQL would be better.

Wonderful. Thanks again for your help!

~RLG

 

[lbass]

Subsqueries created in a SQL expression do NOT take into account the record selection formula for the main report-you must add that into the query. Just use:

(
select max("App_DtTm")
from Schedule A
where A."Pat_ID1"="Schedule"."Pat_ID1" and
"A"."Activity" <> 'NONE' and
"A"."SchStatus_Hist_SD" not in ('N','X') and
{fn month("A"."App_DtTm")} >= 5 and
{fn year("A"."App_DtTm")} =2011
)

Note also the use of the alias table in all but the line that establishes the faux group (1st where clause line).

-LB