Need 1st record of a group but not if repeated. 1

[gallobask]

I have to create a report looking for the first surgery case in a room. I can get that easily enough, but if a surgeon is the first doc working in 2 different rooms I only want his earlier start case.

Example: Grouping on OR Room (Using a variable to count the records and suppressing anything > 1)
OR 1 07:30 Dr. White
OR 2 07:15 Dr. Red
OR 3 08:00 Dr. Blue
OR 4 07:30 Dr. Black
OR 5 08:30 Dr. Red
OR 6 07:30 Dr. Brown

So I only want the 07:15 case for Dr. Red to show. How can I suppress the second Dr Red record?

Thank you!

 

[lbass]

Create a formula like this and place it in the group header:

whileprintingrecords;
stringvar array doc;
numbervar i := i + 1;
numbervar cnt;
if {table.doc} in doc then
cnt := 1 else
cnt := 0;
if not({table.doc} in doc) then (
redim preserve doc[ubound(doc)];//assumes less than 1000 docs
doc := {table.doc}
);
cnt

Then go into the section expert->group header->suppress->x+2 and enter:

whileprintingrecords;
numbervar cnt;
cnt = 1 //note no colon

-LB

 

[gallobask]

I got an error message saying that the subscript must be between 1 and the size of the array.

 

[lbass]

So are there more than 1000 records involved here? Are you doing this within some outer group where you could reset the array contents?

-LB

 

[gallobask]

It is only for one days worth of data so it can't be over 1000. I am thinking the formula is returning a value of 0.

 

[lbass]

Please post the formula that you actually used and explain where you placed it.

Also, can the doctor field ever be null for a particular OR?

-LB

 

[gallobask]

Actually, someone suggested to take out the ubound(doc) and replace with 1000. I did and it worked. But I'm not sure why.
This is placed inthe group header.

whileprintingrecords;
stringvar array doc;
numbervar i := i + 1;
numbervar cnt;
if {empmas.emp_lastnm} in doc then
cnt := 1 else
cnt := 0;

if not({empmas.emp_lastnm} in doc) then
(
redim preserve doc[1000];
doc := {empmas.emp_lastnm}
//assumes less than 1000 docs
);
cnt

 

[lbass]

You are only getting the first 1000 groups then--why are there so many? Are you limiting records in your selection formula?

-LB

 

[gallobask]

We were just trouble shooting - because it wasn't working the way it was originally. At first we used the i variable as the subscript for the array but that ended up getting an error when the array became over 1000. To compensate for that we think we can reset the variables in the array on change of date grouping. So then we have it working. The other new issue than is we need it to suppress the record that has the latest start time. Right now it just suppresses the second record. In the example I gave it is fine to suppress the second one. On a real date the second case actually had the earlier start time and that was the one suppressed.

 

[lbass]

Oops. Sorry about that. Try a different approach. The following assumes you are grouping by date. Create a SQL expression {%mintime}:

(
select min(`time`)
from table A
where A.`date` = table.`date` and
A.`doc` = table.`doc`
)

Then in the main report use a record selection formula like this:

{table.time} = {%mintime}

This should return only the record with the earliest time per doctor per date.

-LB

 

[gallobask]

Are you saying to create a sql expression in crystal somewhere?

 

[lbass]

Yes. Field explorer->SQL expression->New.

-LB

 

[gallobask]

I got it to work!
Now one more thing...I get the first start per doc per date but I only one one doc per date per OR Room.

I get data like this:

OR 10 Dr. Donthi 7:30
OR 12 Dr. Farless 7:30
OR 12 Dr. Zog 8:30

This is the earliest or min start time for each doc, but I only want the first case in the room. So I only want the 7:30 case in room twelve to show.

I can sort by room and then start time and suppress if previous room = room but is there a way to do this through the sql expression as well. Because I want to do some formulas on the records returmed and it gets really messy using running totals etc. with suppressed records.

Thank you for your time on this. I am really learning a lot!!!

 

[lbass]

Create a second SQL expression like this {%minORtime}:

(
select min(`time`)
from table A
where A.`date` = table.`date` and
A.`OR` = table.`OR`
)

Then change your selection formula to:

{table.time} = {%mintime} and
{table.time} = {%minORtime}

-LB

 

[gallobask]

Ok, I need to be sure I understand the formulas.
A.`date` and table.`date` are two different tables with the same date field?

We have a table with the "scheduled" date and time and a sys_time table from logged data that has a scheduled date and time.
%mintime
(
select min(CASEMAS."sh_start")
from casemas
where CASEMAS."sh_date"= sys_time."time_sch_sdate" and
CASEMAS."sh_doclnm" = empmas."emp_lastnm"
)
This returns 25 records - giving me the first scheduled time per doc. If I select on cases with a start time before 10:00 I get it down to 16 records. I have two rooms repeated because there are 2 docs in each room with a first start before 10.


If I add the second formula...
minORtime
(
select min(CASEMAS."SH_START")
from CASEMAS
where CASEMAS."SH_DATE" = SYS_TIME."TIME_SCH_SDATE" and
CASEMAS."SH_ROOM" = SYS_NODE."NODE_MNEMONIC"
)
casemas room is the scheduled data table - sys_node is the logged room name.

This then returns 11 cases. For some reason it completely excludes 2 rooms I should have 13 cases for the date I am running). I have compared the fields between the cases it excludes and I can see no reason why they aren't included.
The room and names are equal.

Here is my select statement:
{CASES.CASS_SITE} = "ACH1" and ( to get the main OR)
({SYS_TIME.TIME_CON_SDATE}) in {?Date Range} and
{CASES.CASS_STATUS} = "L" and (L means the case was logged)
{SYS_TREE.TREE_NODEID} = 1130 and (Gives me the room time - I compare to the scheduled time)
{SYS_NODE.NODE_TYPEID} = 1019 and (Gives me the logged room)
(({SYS_TIME.TIME_SCH_STIME} = {%mintime})
and {SYS_TIME.TIME_SCH_STIME} = {%minORtime})
and {CASEMAS.SH_START} <= "10:00"

I don't know that you can help me any further without seeing the data....any suggestions?

 

[lbass]

No, {%mintime} should be:

(
select min("sh_start")
from CASEMAS A
where A."sh_date" = CASEMAS."sh_date" and
A."sh_doclnm" = CASEMAS."sh_doclnm"
)

...assuming that sh_doclnm = doctor. The second one should be:

(
select min("SH_START")
from CASEMAS A
where A."SH_DATE" = CASEMAS."SH_DATE" and
A."SH_ROOM" = CASEMAS."SH_ROOM"
)

You should be able to copy the above into the SQL expression are with no changes.

I didn't examine more than this for now. Try these changes first.

-LB

 

[gallobask]

Oh man - almost there!!!
This didn't return 2 rooms - but I think I know why. The sql statements are working as they should but the first case each of the 2 rooms is not a "real" case. It is a scheduled case with no surgeon name. It is used just to hold a place on the scheduling grid. Is there a way to write into the sql statement to only select cases with a status of "S"? Real cases are status "S". The place holders are status "R" for reserved. I tried using the main select statement to only pull status "S" cases but it didn't chnge the records returned.
Thank you!!!!

 

[lbass]

(
select min("sh_start")
from CASEMAS A
where A."sh_date" = CASEMAS."sh_date" and
A."sh_doclnm" = CASEMAS."sh_doclnm" and
A."status" = 'S'
)

Same addition to the other one.

-LB

 

[gallobask]

I get an error: Invalid column name "status". Does it have to be named in the select line?

 

[lbass]

In your last post, you mentioned that status must = "S"--so replace "status" with your field name, but leave the table name as "A", as in:

A."yourstatusfield" = 'S'

However, if the status field is in a different table, then that table would have to be built into the expression.

-LB