INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Grouping on a Formula Using Minimum Date 2

## Grouping on a Formula Using Minimum Date

(OP)
Hi,
I have created a report in Crystal 13. I have a formula that is used to tell how many days between two dates. The "Admit Date" is constant, but the second date could have multiple values. I am using the minimum(second date) in my datediff.
The first formula is to get the minimum date.
@MinOrderDate
minimum({ORDER_MED.ORDER_INST})

The datediff is working fine.
@screen2nutorder2
((datediff('n',{IP_FLWSHT_MEAS.RECORDED_TIME},{@MinOrderDate}))/60)/24

The third formula is what I want to use to group the results of the datediff.
@newONSgroup
if{@screen2nutrorder 2} in 0 to .9999 then 0
else
if {@screen2nutrorder 2} in 1 to 1.9999 then 1
else
if {@screen2nutrorder 2} in 2 to 2.999 then 2
else
if {@screen2nutrorder 2} in 3 to 3.999 then 3
else
if {@screen2nutrorder 2} in 4 to 4.999 then 4
else
if {@screen2nutrorder 2} in 5 to 5.999 then 5
else
if {@screen2nutrorder 2} in 6 to 6.999 then 6
else
if {@screen2nutrorder 2} >= 7 then 7

When I try to create the group using the third formaula, I get a message that says..
Group Specified on a non recurring field.
I have found some post on this message, but nothing that really helps. Is what I am trying to do possible?

Thank you,
Chip

### RE: Grouping on a Formula Using Minimum Date

The issue is that grouping is somewhat based on a aggregate values (@MinOrderDate). This is an issue because grouping is done before aggregates. Normally I use a command (SQL code) to get around this issue.

### RE: Grouping on a Formula Using Minimum Date

You could potentially use a SQL expression to return the minimum. Your formula indicates that you are looking for the minimum order date for the report as a whole, since you don’t include a group argument—is this your intention? The syntax for the expression varies based on version of CR and your database and driver.

-LB

### RE: Grouping on a Formula Using Minimum Date

(OP)
Hi kray4660,

Could you give me an example of how you would accomplish this with sql code? I have a main table that the order table(order date) is linked to. If I understood how to link the code back into the main table, I could do it. I believe that you would create a little query that would look for the specific medication orders and grab the minimum order date for a specific patient encounter. That query could link back to the main table via the patient encounter number. Can you steer me in that direction?

Thank you!

### RE: Grouping on a Formula Using Minimum Date

(OP)
Thank you lbass and kray4660!

### RE: Grouping on a Formula Using Minimum Date

(OP)
My intention is to get a specific patient encounter based off of the admission date. From there, I am looking for a specific documentation record and that date. I am using the specific documentation date(only one) and the minimum order date to calculate how long it took from the documentation date to the first order date. The duration of that datediff is what I want to group by. In a crosstab, I need to show the value for the datediff and a count of the patient encounters for each of the grouped datediff.

Thank y'all for working with me on this!

### RE: Grouping on a Formula Using Minimum Date

So are you saying that you only one patient encounter will appear on the report and NOT multiple encounters or multiple patients?

-LB

### RE: Grouping on a Formula Using Minimum Date

(OP)
No, there will be one encounter per patient and multiple patients. I will suppress all of the detail and just have the summary crosstab. This is what it will sort of look like.

The first column are the values from the grouped datediff values. The fourth column is the count of the patient encounters that fell into each group.

Thank you!

### RE: Grouping on a Formula Using Minimum Date

You need to group on patient first in the main report. Then go into the field explorer->SQL expression and enter something like this (punctuation depends upon your database). Plug in your actual fields, but use the alias table names ("a" and "b") where shown:

//{%mindate}:
(
select min(a."ORDER_INST")
FROM "ORDER_MED" a, "PATIENTTABLE" b,
where a."PATIENTID"=b."PATIENTID" AND
b."PATIENTID"="PATIENTTABLE"."PATIENTID"
)

This assumes you have a patient table that is linked to the ORDER_MED table on patient ID. If you need further help on constructing this, please provide the exact table and field names along with the record selection formula you are using.

This will return the minimum date per patient. Then plug this into your formula instead of the minimum. You can now use this as a field in your crosstab.

-LB

### RE: Grouping on a Formula Using Minimum Date

(OP)
Thank you LB!
I am working through some of the syntax right now. I am going to try to get it to work.

Chip

### RE: Grouping on a Formula Using Minimum Date

(OP)
LB, you are awesome! You have helped me several times over the past 13 years! Thank you!

Your example worked perfectly. I added the medication IDs to the query and changed the syntax a little. It works great! It is exactly what I needed. Thanks again! This will help me get out of the box a little more when I get in a situation like this. I really appreciate it!
I put an orange box around a portion of the query. That section is what links back up to the PAT_ENC_HSP table in the main report, right?

This is the final query.

### RE: Grouping on a Formula Using Minimum Date

Yes, but it is also what creates the group within the SQL query that allows you to get a value for each patient in the main report.

Glad you got it to work.

-LB

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!