Should I use SQL Designer

[csm2]

I am creating a report of open patient enrollments w/o activity for the past nine months. I have the following SQL:

SELECT fields
FROM tables
WHERE DischargeDate is null
and StartOfCaredate < @CutoffDate
and a.patno=b.patno
and LastActivityDate < @CutoffDate
and LastActivityDate in (SELECT max(LastActivityDate)
FROM table
WHERE patno=b.patno

(@CutoffDate = CurrentDate - 9 months)

Can I code this SQL in Report Designer or should I code part of it in SQL Designer to do a rough select and then bring in the resulting record set into Report Designer for further refining of selection?

How would I code the sub-query in Report Designer?

I am using Informix and SeagateInfo 7.5

 

[kenhamady]

You can certainly do this in the SQL designer.
I dont' think you can do it in the report designer because it only allows limited changes to the SQL that it creates. I don't think it supports subqueries. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[csm2]

Ken,
Correct me if I'm wrong but SQL Designer does not support formulas. Therefore, since the report has records older than 9 months, that part of the selection process should take place in Report Designer.

 

[kenhamady]

That is correct, however SQL statements can have parameter prompts. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[miked123]

Create a view on your server using your sql statement. Make sure you Select only the fields that you need in your view. Create your CR using this view. You can then use the Record or Group selection in CR to restrict more rows.
Miked