Need group formula help!

[vngpal]

Hi All,
I need to create new groups based on some criteria mentioned as below. I am using CR8.5 and SQL Server database.

The database is set as below.
Site is the top order and can have Gyn, Neuro, Cardio and so on.
Under the Site lot of patients are admitted.
Under each pattient there are lot of times like surgery start time. surgery end time, Anesthesia start time, Anes end time, room start time, room end time and so on. Each category like surgery, anes or room they are associated with catid. For example surgery is associated with 1121, anes is associated with 1223 and so on.

Now I nned to create a new site based on the category and their associated times.
This is only for Gyn site. For other site we use the system site only.
Each category may have start time, end time or may not have any time.

First criteria: If the category is Surgery(1121) and if the start time and end time are not null the I need to create a group as GynSurg and only this category should got to this new group. Also if there is no time in any category then these categories should to be associated with GynSurg site.

Second Critera: If the category is Anes(1223) and the start time and end times are not null then this category has to be associated with GynAnes.

Third category: If the category is in Surgery(1121) and Anes(1223) and if bot the categories have statr time and end times then thse two catogaries have to be associated with GynSurg.

If a patient has more categories other than Surgery and Anes then all these categories should be associated with GynSurg site.

If it is confusing please let me know. I can provide more examples.

Thanks in advance.

Pal

 

[lbass]

Yes, too confusing. Please show a sample of how the data currently displays and then a second sample of how you want it to display.

-LB

 

[vngpal]

LB,
I am soory that I could not send the sample in time. Here is the sample.

Original group order

Group(Site) GYN
Pt NODE ST ET
100012 1121
100012 1223 7.00 8.00
267120 1121 5.15 6.45
267120 1223
267345 1121 3.47 8.45
267345 1223 2.32 3.01
289410 1121 2.23 3.51
289410 1223 1.55 2.15
315789 1121
315789 1223 1.10 2.05
326100 1121 2.36 5.52
326100 1223 1.46 3.09
327441 1121
327441 1223 11.10 11.46
327875 1121 1.48 2.22
327875 1223


New group order
Group(new site) GYNSURG
Pt NODE ST ET
267120 1121 5.15 6.45
267120 1223
267345 1121 3.47 8.45
267345 1223 2.32 3.01
289410 1121 2.23 3.51
289410 1223 1.55 2.15
326100 1121 2.36 5.52
326100 1223 1.46 3.09
327875 1121 1.48 2.22
327875 1223


Group(new site) GYNANES
100012 1121
100012 1223 7.00 8.00
315789 1121
315789 1223 1.10 2.05
327441 1121
327441 1223 11.10 11.46


Hope this will be clear now.
Thamks and appreciate your help.
Pal

 

[lbass]

The problem is that you want to group based on a conditions that cross rows within pt number. You could create clusters by creating a formula like this {@surgstart}:

if {table.site} = "GYN" and
{table.node} = 1121 then {table.ST}

Place this in the detail section and insert a maximum on it at the pt group level (insert a group on this if you haven't). Then go to report->group sort and choose maximum of {@surgstart} as the group sort.

You can then create labels for the new groups by creating a formula {@name}:

whileprintingrecords;
stringvar currname;
stringvar prevname := currname;

if {table.site} = "GYN" then
(
if isnull(maximum({@surgstart},{table.patientno})) or
maximum({@surgstart},{table.patientno}) = "" then
currname := "GYNANES" else
currname := "GYNSURG"
);
currname

Insert another group_header_a for the patient group and place {@name} there. Then go to the section expert->patient group_header_a->suppress->x+2 and enter:

whileprintingrecords;
stringvar prevname;
{@name} = prevname;

-LB

 

[vngpal]

Thanks for the solution. It works fine but not for the last patient, As per the sample data 327441 has to come under GYNANES but it shows up in GYNSURG.

Otherwise your solution works h=great. Any idea?

Thanks

 

[vngpal]

My bet. I made some error. Yours works fine.
Thanks a lot.
Pal

 

[vngpal]

LB,
One more addition to this. Now my manager wants to get the data for a date range period. Also he wants the group should be as below. The above works fine if I don't have another group. But the situation is different and wants to add another group.

Group1 (New Site)
Group2 (Date)
Group3 (as you said patient number)

Thanks and appreciate your suggestions,
Pal

 

[lbass]

Since the above "grouping" is based on the presence/absence of start and end datetimes, the insertion of a date group will make the other group impossible, since all nulls/blanks would be grouped together--in other words the two records for anesthesia and surgery would be separated.

Also, what would the date group be based on? Which set of dates?

-LB

 

[vngpal]

Thanks for the info. The dates may range from any start date to end date based on the user selection. So I have to add either date range parameter or two parameters as startdate and enddate. But the grouping should be as mentioned in the previous post.

Thanks,
Pal

 

[lbass]

Base on which dates--for surgery or anesthesia?

-LB

 

[vngpal]

Both.
For example:

Group1 (GYNANES)
Group2 (Date in ascending order)
Group3 (Patient Number)

Group1 (GYNSURG)
Group2 (Date in ascending order)
Group3 (Patient Number)

This is how the data should be displayed.

Thanks,
Pal

 

[lbass]

Sorry, but this makes no sense. First the Gynanes and Gynsurg are not actual groups, and secondly the point is that there are two sets of dates within each patient group that actually define the fake group for anes and surg. Adding a group on dates disrupts that definition. You would have to take an entirely different approach to add a group on dates and that group would have to be limited to the dates for anesthesia or for surgery. You would have to decide which.

-LB

 

[vngpal]

Thanks for the response. I appreciate your help. Let me think anything else is possible. Have a nice day.
Pal