date range formula 2

[lrather]

Hi!

I am looking for a date range formula to determine the following information:

(I am using crystal v 8.0)

I use the report expert to design my reports.

I need a formula that will give me a client list; of active clients not seen within this period 11/01/02 thru 01/31/03.

(No services within that period. Chart # Name.)

If someone could help me I would be very thankful. I am new at using crystal reports.

Thank you to whom ever takes this task.

LR

 

[lyanch]

Showing things not in a period is pretty tricky in CR. I would create a Stored Procedure for this which would be much more effecient. If you absolutely cannot use a stored procedure, reply and someone can go through using subreports and suppresion with you...

Lisa

 

[lrather]

I have not used stored procedures before. I am new at this crystal reporting. Can it not be done through report expert? the data Iam trying to get:

Active client not seen with in a 90 days. Jan 1,2003 thru Mar 31, 2003.

Thanks
LR

 

[synapsevampire]

I think you should be able to handle this, depending upon your table layout.

Please post example data.

-k

 

[lbass]

How do you define "active"? For this example, let's assume that by active you mean anyone who was seen in the month preceding the time period in question. Then, you would select a date range:

{table.date} in Date(2002, 10, 01) to Date (2003, 01, 31)//this includes the "active" period

Create a formula {@seenornot}:

if {table.date} >= Date(2002, 11, 01) then 1 else 0//place in details section

Group on {table.clientname} and suppress the details section.

Then go to edit record selection->group and enter:

sum({@seeornot}, {table.clientname}) = 0

You now have a list of clients who were active in October, but were not seen in the three months following.

-LB

 

[lrather]

Thanks for your reply.

By Active Client; I mean a current client that has not had a service or been seen by the doctor, in the date period of Jan 1,2003 thru March 31, 2003.

Example: this would be a client seen.
(chart #)00000 name(Jon Doe) )Date Seen)01/03/03 (Type of service)304(could be any code.

This would be a client no seen within the date range:
00000 name. (They would not show a service)

I don't know if I am making my self clear. please let me know.

Thanks,
LR

 

[lbass]

What I meant was, do you want to include clients from years ago? What do you consider an "active" or "current" customer--those seen at some time within the last year? two years? six months? And are you evaluating customers whose last visit was before the period of interest or after the period of interest? You changed your example dates, which is confusing.

In order to do this report, the client has to appear in the database at some point in time, but not during the period in question--I'm just trying to understand what the time period is that makes you count them as a current client.

-LB

 

[lrather]

I am sorry, they changed the date on me. In my database there is a field called admstat_c if this field has an A it shows the client Active. I want to know if the active client has had a service within the 3 month period. Which would be Jan 1, thru Mar 31, 2003. I am sorry for the confusion.

Thanks,
Lr

 

[lbass]

In order to get the name of the active clients, use the following for your record selection and do not use a date period at all:

{table.admstat_c} = "A"

Create a formula {@seenornot}:

if {table.date} in Date(2003, 01, 01) to Date(2003, 03, 31) then 1 else 0//place in details section

Group on {table.clientname} and suppress the details section.

Then go to edit record selection->group and enter:

sum({@seeornot}, {table.clientname}) = 0

You now have a list of clients who were active either before or after the three month period of interest, but were not seen during those three months. If you want to exclude people who were seen only after the three month period, i.e., new customers who were seen on or after 4/1/03, then add the date selection to your record selection formula: {table.date} < Date(2003, 04, 01)

Please try this and let me know your results.

-LB

 

[lrather]

The formula {@seenornot} does not work? or I am not understanding what you mean.

Do I type everything from the table.admstat to the if statement in one formula? I am really confused.

Thanks,
LR

 

[lbass]

You should substitute the name of your database field for the first part of this formula, so &quot;table&quot; will change to your table name--maybe &quot;admstat&quot; is your tablename and &quot;c&quot; is the field name, I don't know. Enter this as the record selection formula or use the dropdown list to choose the field and then type in &quot;A&quot;

{table.admstat_c} = &quot;A&quot;

Then go to the field explorer->formula fields->new and create {@seeornot}, substituting the name of the date field in your database for {table.date}:

if {table.date} in Date(2003, 01, 01) to Date(2003, 03, 31) then 1 else 0

After saving the formula, drag it from the field explorer to the details section.

Does that answer your questions? Let me know if you are still confused...

-LB

 

[lrather]

Thanks a lot for all your help... It worked.


Thanks,

LR