How do I convert this SQL Statement to a formula

[Hypermommy]

Hi there all,

Our DBA has given me the SQL statement to get the admissions tax credit but I'm unsure what (if anything) I can do with it in Crystal. I need to make a formula of it so I can show the resulting number on the report but am at a complete loss as to how to do so.

The SQL statement he gave me is:

select sum(dtadtax)
from R301_handle_dist where dtascode = [current permitholder] and
dtpdate beteween [begin date] and [end date] and
dtetype = [current performance type]
and dtpdate <=
(
select tcdate from r211_tax_credit where ascode = [current permitholder]
)

The stuff in [] represent the user input fields I have. The current permitholder and current performance type are groups and the selection formula uses the begin date and end date to make sure I'm only pulling records for those dates.

So... how do I do this SQL statement in Crystal so that it will pull the right number for each change in permitholder and/or performance type?

Thanks a billion for any help you can give.

-= Hypermommy =-

 

[synapsevampire]

At minimum, please post the version of Crystal and the database used.

In CRE 9 it's fairly simple as you can use most of the SQL (parameters would be different), in earlier versions considerably more complex, and may require a subreport (the subquery would be the equivalent of the subreport).

You might just ask the DBA to supply a Stored Procedure, that would be the fastest and simplest.

-k

 

[Hypermommy]

Darn.... every time I think I've posted everything I leave something off . I am using Crystal 9 and a SQL Server database. Please (when you get a moment) let me know how to do this, both how to access the results of a Stored Procedure (never did that before) and how to do it using only Crystal 9 and get the results into the report.

Thanks a billion for the help!!!!

-= Hypermommy =-