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!

 

[gallobask]

I fixed the error. "status" should have been "sh_schstat".
It pulls all the cases for the test date I have been using. I'm going to try a few more days to test. Hopefully it's working now!

Thank you for all of your help!!!!!!!!!!!
I really want to find out more about how to use these sql statements to limit record selection. Any suggestions?

 

[gallobask]

ACK!
One more thing.
A doc can be scheduled at more than one site on the same day. I want to be able to run the report by site and get the first start per doc per room. If I run it for a site that has a later start then the same docs case at another site it will not pull the first the first case for both sites.
Ex:
Site ACH
OR 1 dankoff 7:30

Site ASC
SSC 3 dankoff 8:30
SSC 4 smith 8:00

Both of these are first cases for Dr Dankoff at each site. The report will only return the 7:30 case at ACH. If I run it for ASC the 8:30 case does not show.

I tried adding another sql expression based on the site field but it didn't work. No error but it only returned the doc that was not at both sites. So in the example above it for site ASC it only returned smith. So I think I have the logic backwards somehow....
(
select min("sh_start")
from CASEMAS A
where A."sh_date" = CASEMAS."sh_date" and
A."sh_site" = CASEMAS."sh_site" and
A."sh_schstat" = 'S'
)

Thank you again.

 

[lbass]

You left out the faux group for doctor. Should be:

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

-LB

 

[gallobask]

I had tried that as well. it still only returns the earliest time per doc regardless of site. So if a doc is at 2 sites and I run the report for each site separately. It will only return one record and it will show on the report for the site that has the earliest time. I need it to the earliest time per doc per room per site.

 

[lbass]

If you are running the report separately by site, you need to specify the site (e.g., A."sh_site" = 'ABC') in the expressions or you need to run the report for all sites, perhaps adding an outer group on site, for example.

-LB

 

[gallobask]

I tried running the report for all sites, It returns the same data just grouped by site. So it doesn't show the first case starts for each site per doc. I suppose worst case I can make 2 separate reports one for each site and modify the sql expressions in each accordingly. I assume it won't work with the sql expression since it is only returning one value per doc. I can't aske it to return 2 values just because there are different sites correct?

Well I still learned a lot and the report processing time is 100 persent improved. Thank you so much for all of the time you put into this. If you know of any good resources to explain these sql expression let me know!!!

Thanks again!!!!!!!!!!

 

[lbass]

I think you are implementing this incorrectly. Please post the two expressions that build in the site and also post your record selection formula.

-LB

 

[gallobask]

Ok here is the info:

%minORtime
(
select min("SH_START")
from CASEMAS A
where A."SH_DATE" = CASEMAS."SH_DATE" and
A."SH_ROOM" = CASEMAS."SH_ROOM" and
A."sh_site" = CASEMAS."sh_site" and
A."sh_schstat" = 'S'
)
%mintime
(
select min("sh_start")
from CASEMAS A
where A."sh_date" = CASEMAS."sh_date" and
A."sh_doclnm" = CASEMAS."sh_doclnm" and
A."sh_site" = CASEMAS."sh_site" and
A."sh_schstat" = 'S'
)
Select statement
({CASEMAS.SH_START} = {%mintime}) and
{CASEMAS.SH_START} = {%minORtime} and
{CASEMAS.SH_START} <= "10:00" and
{CASEMAS.SH_DATE} = {?Date Range} and
{SYS_TREE_2.TREE_NODEID} = 1130 and
{@DOW} in ["Friday", "Thursday", "Wednesday", "Tuesday", "Monday"] and
{SYS_TIME.TIME_CON_STIME} <> "" and
{CASES.CASS_SITEID} = {?Site} and
{CASEMAS.SH_ROOM} in ["SSC 4", "SSC 3", "SSC 2", "SSC 1", "OR 17", "OR 16", "OR 15",
"OR 14", "OR 12", "OR 11", "OR 10", "OR 09", "OR 08", "OR 07", "OR 06", "OR 05",
"OR 04", "OR 03", "OR 02", "OR 01"] and
({?Service} = "ALL" or
{CASES.CASS_SERVICE} = {?Service})

 

[lbass]

I don't see any problems here. How do the results display compared to how they should? Can you show examples?

-LB

 

[gallobask]

My bad...Actually now it is picking up the duplicate doc (Dankoff)I played with it so much I didn't realize that part was now working... So sorry for my confusion.
Thank you again for you time and patience!!! You have been awesome!
ASC
Date Room Sched Surgeon
11/15/2010 SSC 4 08:45 DANKOFF, JOSEPH
11/15/2010 SSC 3 08:00 SPEAR, KEVIN

ACH
Date Room Sched Surgeon
11/15/2010 OR 04 07:00 BARANEK, ROBERT
11/15/2010 OR 01 08:00 BENDER, ELIZABETH
11/15/2010 OR 10 08:00 CIRALDO, ALFRED
11/15/2010 OR 14 09:00 CULLADO, MICHAEL
11/15/2010 OR 17 07:30 DANKOFF, JOSEPH
11/15/2010 OR 07 09:00 DEBSKI, ROBERT
11/15/2010 OR 12 07:30 DONTHI, RAMA

 

[gallobask]

Ok, now one more thing.....
It is completely excluding one doc. He only has one case at the ASC. All of the data in the record looks correct. If I comment out the mintime it shows up for ASC but the same report for the ACH shows all the duplicate docs.

Here is the ACH data without the %mintime
- Williams is duplicated
Date Room Sched Surgeon
11/15/2010 OR 04 07:00 BARANEK, ROBERT
11/15/2010 OR 01 08:00 BENDER, ELIZABETH
11/15/2010 OR 10 08:00 CIRALDO, ALFRED
11/15/2010 OR 14 09:00 CULLADO, MICHAEL
11/15/2010 OR 17 07:30 DANKOFF, JOSEPH
11/15/2010 OR 07 09:00 DEBSKI, ROBERT
11/15/2010 OR 12 07:30 DONTHI, RAMA
11/15/2010 OR 15 07:30 FLORA, ROBERT
11/15/2010 OR 16 08:00 KUTNICK, STEVEN
11/15/2010 OR 03 08:00 VANFOSSEN, VICTORIA
11/15/2010 OR 08 08:00 WAGNER, DOUGLAS
11/15/2010 OR 05 09:00 WILLIAMS, GARY
11/15/2010 OR 06 08:00 WILLIAMS, GARY
11/15/2010 OR 09 07:00 ZOGRAFAKIS, JOHN
But Daltons case shows up for th ASC.
Date Room Sched Surgeon
11/15/2010 SSC 1 09:30 DALTON, ARTHUR
11/15/2010 SSC 4 08:45 DANKOFF, JOSEPH
11/15/2010 SSC 3 08:00 SPEAR, KEVIN

So if mintime is used I get one doc for the ACH but no Dr Dalton for the ASC.
Date Room Sched Surgeon
11/15/2010 SSC 4 08:45 DANKOFF, JOSEPH
11/15/2010 SSC 3 08:00 SPEAR, KEVIN

I just don't get it...

 

[lbass]

I can't tell what I'm seeing here. Please show the data grouped by site, OR, and date.

-LB

 

[gallobask]

Here are the 2 statements and the data sorted by site,room then date.I only ran for 11/15. I am missing the 9:30 Dr Dalton case in ASC Room SSC 1.
%mintime
(
select min("sh_start")
from CASEMAS A
where A."sh_date" = CASEMAS."sh_date" and
A."sh_doclnm" = CASEMAS."sh_doclnm" and
A."sh_site" = CASEMAS."sh_site" and
A."sh_schstat" = 'S'
)
%minORtime
(
select min("SH_START")
from CASEMAS A
where A."SH_DATE" = CASEMAS."SH_DATE" and
A."SH_ROOM" = CASEMAS."SH_ROOM" and
A."sh_site" = CASEMAS."sh_site" and
A."sh_schstat" = 'S'
)
Site Date Room Sched Surgeon
ACH 11/15/2010 OR 01 08:00 BENDER, ELIZABETH
ACH 11/15/2010 OR 03 08:00 VANFOSSEN, VICTORIA
ACH 11/15/2010 OR 04 07:00 BARANEK, ROBERT
ACH 11/15/2010 OR 06 08:00 WILLIAMS, GARY
ACH 11/15/2010 OR 07 09:00 DEBSKI, ROBERT
ACH 11/15/2010 OR 08 08:00 WAGNER, DOUGLAS
ACH 11/15/2010 OR 09 07:00 ZOGRAFAKIS, JOHN
ACH 11/15/2010 OR 10 08:00 CIRALDO, ALFRED
ACH 11/15/2010 OR 12 07:30 DONTHI, RAMA
ACH 11/15/2010 OR 14 09:00 CULLADO, MICHAEL
ACH 11/15/2010 OR 15 07:30 FLORA, ROBERT
ACH 11/15/2010 OR 16 08:00 KUTNICK, STEVEN
ACH 11/15/2010 OR 17 07:30 DANKOFF, JOSEPH
ASC 11/15/2010 SSC 3 08:00 SPEAR, KEVIN
ASC 11/15/2010 SSC 4 08:45 DANKOFF, JOSEPH

 

[lbass]

I don't see anything wrong. What does the data for the above sample look like if you remove the SQL expressions from the selection criteria? specifically, can you show the raw data for the missing doctor (any room) and also show the results for SSC1?

-LB

 

[gallobask]

Here is the data with the 2 sql expressions commented out.
The select criteria is looking for cases on 11/15 with a scheduled start time of 10:00 or less.
You can see the Dalton case now shows up for ASC.
Site Date Room Sched Surgeon
ACH 11/15/2010 OR 01 08:00 BENDER, ELIZABETH
ACH 11/15/2010 OR 01 09:00 BENDER, ELIZABETH
ACH 11/15/2010 OR 01 10:00 BENDER, ELIZABETH
ACH 11/15/2010 OR 03 08:00 VANFOSSEN, VICTORIA
ACH 11/15/2010 OR 04 07:00 BARANEK, ROBERT
ACH 11/15/2010 OR 05 09:00 WILLIAMS, GARY
ACH 11/15/2010 OR 06 08:00 WILLIAMS, GARY
ACH 11/15/2010 OR 07 09:00 DEBSKI, ROBERT
ACH 11/15/2010 OR 08 10:00 VANFOSSEN, VICTORIA
ACH 11/15/2010 OR 08 08:00 WAGNER, DOUGLAS
ACH 11/15/2010 OR 09 07:00 ZOGRAFAKIS, JOHN
ACH 11/15/2010 OR 09 10:00 ZOGRAFAKIS, JOHN
ACH 11/15/2010 OR 10 09:30 CIRALDO, ALFRED
ACH 11/15/2010 OR 10 08:00 CIRALDO, ALFRED
ACH 11/15/2010 OR 12 07:30 DONTHI, RAMA
ACH 11/15/2010 OR 12 09:00 MITCHELL, RICHARD
ACH 11/15/2010 OR 14 09:00 CULLADO, MICHAEL
ACH 11/15/2010 OR 15 07:30 FLORA, ROBERT
ACH 11/15/2010 OR 15 10:00 MOAWAD, JOHN
ACH 11/15/2010 OR 16 08:00 KUTNICK, STEVEN
ACH 11/15/2010 OR 16 09:30 KUTNICK, STEVEN
ACH 11/15/2010 OR 17 07:30 DANKOFF, JOSEPH
ASC 11/15/2010 SSC 1 09:30 DALTON, ARTHUR
ASC 11/15/2010 SSC 3 09:00 SPEAR, KEVIN
ASC 11/15/2010 SSC 3 08:00 SPEAR, KEVIN
ASC 11/15/2010 SSC 4 10:00 DANKOFF, JOSEPH
ASC 11/15/2010 SSC 4 08:45 DANKOFF, JOSEPH

 

[lbass]

I see no reason for Dr. Dalton not to appear--unless the status <> "S"--you didn't show the status in your sample data.

Please remove the comments and post your entire record selection formula.

-LB

 

[gallobask]

{CASEMAS.SH_SCHSTAT} = "S" and
{CASEMAS.SH_START} <= "10:00" and
{CASEMAS.SH_DATE} = {?Date Range} and
{SYS_TREE_2.TREE_NODEID} = 1130 and
{@DOW} in ["Friday", "Thursday", "Wednesday", "Tuesday", "Monday"] and
{SYS_TIME.TIME_CON_STIME} <> "" and
{CASEMAS.SH_ROOM} in ["SSC 4","SSC 3","SSC 2","SSC 1","OR 17","OR 16","OR 15",
"OR 14","OR 12","OR 11","OR 10","OR 09","OR 08","OR 07","OR 06","OR 05",
"OR 04","OR 03","OR 02","OR 01"] and
({CASEMAS.SH_START} = {%mintime}) and
{CASEMAS.SH_START} = {%minORtime}

 

[lbass]

I don't see anything wrong. Is it possible that the time was entered as PM, i.e., 9:30 pm?

-LB

 

[gallobask]

No because we use military time. So these are all am cases. I know it didn't make any sense to me - but I don't know much! If I comment out the % lines it shows up!

 

[lbass]

Do you have any conditional section suppression? Or a group selection formula? Otherwise I see no reason for this behavior.

-LB