Please help with Inactivity report

[deniscok]

I am looking for any ideas on how to create the following report.

I am using a SQL command query to pass Clients, Salesperson and transaction date to Crystal reports.

I need to allow the user to select parameters for Salesperson and a transaction from / to date range.

I need the report to show only the clients that DO NOT have a transaction between the two date ranges. Example:

report date parameters are 1/1/2014 to 8/31/2014

Client A has transactions in 2012 -- this client should appear on the report​

Client B has no transactions -- this client should appear on the report​

Client C has transactions in 2012 and 2014 -- this client should not appear on the report​

Client D has pending future transactions in November 2014 -- this client should not appear on the report​

The problem that I keep running into is that the example client C and D

I have been able to create the necessary query in SQL (shown below), but I cannot get the date parameters to pass to Crystal in the command script and I cannot figure out how to recreate this solely in Crystal,


SELECT DISTINCT
c.account,
c.Clientname,
c.street,
c.city,
c.state,
c.zipcode,
c.phone,
c.email,
c.salesperson as AssignedSP,
ha.Salesperson AS SellingSP,
ha.Ticket,
ha.TranDate,
ha.Amount

FROM clients c
LEFT JOIN (SELECT
Account,
Salesperson,
Ticket,
TranDate,
Amount,
RevenueCode

FROM History_Ad where revenuecode <> ' ') ha on ha.Account = c.account


where c.siteID = 3 and 0=(select count(*) from history_ad ha2 where ha.Account = ha2.Account and ha2.TranDate BETWEEN '2014-01-01' and '2014-08-31')

Any thoughts or ideas would be greatly appreciated

 

[PeterDimitrov]

You can create a command and paste the SQL there.
As it is now, your SQL will return multiple records per customer and customers with future transactions. Also you have a subquery in the WHERE clause , which will slow the report

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.