Embedded Selects

[jwestmore]

I have a group of accounts and each account has a number of charges associated with it. Each charge has a post date. I want a report that groups charge details by account. The caveat is I only want accounts that have had a charge posted within the last 90 days. However, each account group would have the entire charge history associated with it (including charges over 90 days old).

In SQL, I would use the following statement:

SELECT account_id, charge_total, charge_description, charge_post_date
FROM account INNER JOIN charge ON account.account_id = charge.account_id
WHERE account_id = (SELECT account_id FROM account INNER JOIN charge ON account.account_id = charge.account_id WHERE max(charge_post_date) > dateadd(day,-90,getdate())

Any tips on how to get this result in Crystal?

 

[synapsevampire]

That would depend upon your version of Crystalk, and the database being used.

It looks like you're using SQL Server.

If you have CR 9 or above, you can paste the SQL in.

If you have the permissions, or a reasonable dba, have a View created.

If you're using CR 8.5 and the dba is a Nazi, you can use a SQL Expression to create the nested select.

Please post your CR version and the database used on future posts so that people won't have to try to cover every version of the software.

-k