Grouping on a Formula Using Minimum Date 2

[chiplarsen]

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

 

[kray4660]

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.

 

[lbass]

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

 

[chiplarsen]

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!

 

[chiplarsen]

Thank you lbass and kray4660!

 

[chiplarsen]

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!

 

[lbass]

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

-LB

 

[chiplarsen]

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!

 

[lbass]

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

 

[chiplarsen]

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

Chip

 

[chiplarsen]

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.

 

[lbass]

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