create report based on sql statement

[crystalnewbie]

I have this sql query that I'm trying to convert into a cr8.5 report. I'd like to learn how it can be done without pasting the sql query directly into the sql editor. Need assistance on getting started though.

The sql basically looks for employees that doesn't have a transaction record in the table since 21 days from the current date.

select empID, max(eventdate) from mytable
where empID in
(
select distinct empID
from mytable
group by empID
having max(eventdate) < getdate()-21
)
group by empID
order by max(eventdate)

 

[GJParker]

Sounds pretty straight forward.

1. add MyTableto the report
2. add empID and eventdate to the details section of your report
3. insert a group based on empID
4. insert a summary calculation into the group header or footer

//@MaxDate
Maximum({myTable.eventdate})

5. Insert a Group Selection formula

Maximum ({MyTable.EventDate}, {MyTable.empID}) < DateAdd ("d",-21 ,CurrentDate )

HTH

Gary Parker
Systems Support Analyst
Manchester, England

 

[crystalnewbie]

Thanks Gary. Indeed, it was pretty straight forward. I do have a concern that this method pulls in all the records from the table and i'm dealing with a few million records.

Is there a way to do the group selection at the database instead instead of doing it at the report level?

 

[GJParker]

I think the only way to do that would be to use the sql from your original post in your Crystal report, or even better if possible, create a view or stored procedure on your database using this sql and create your report from the rsulting data.

HTH

Gary Parker
Systems Support Analyst
Manchester, England