Sql expresion pulling data from records excluded by Record Selection

[GayleC]

Crystal XI
I’m writing a report that includes a field that calculates the difference between two dates. My data contains multiple rows of data related to the same patient encounter with different Charge Entry Dates, I want to pull only data with a certain batch name, then apply the earliest Charge Entry date to all lines. A sample of my data is as follows.

Ticket Number Type BatchName CPT DOS Charge Entry Date
ASH001344 Office Visit EMR ASH BLUM 112310 90471 11/19/2010 11/25/2010
ASH001344 Office Visit EMR ASH BLUM 112310 90472 11/19/2010 11/23/2010
ASH001344 Office Visit EMR ASH BLUM 112310 90658 11/19/2010 11/27/2010
ASH001344 Office Visit EMR ASH BLUM 112310 90715 11/19/2010 11/23/2010
ASH001344 Office Visit EMR ASH BLUM 112310 99204 11/19/2010 11/23/2010
ASH001344 Office Visit jsmith_20101119(1605774075000010) 99214 11/19/2010 11/19/2010

My Record selection formula is:

{Batch.Name} like "EMR*"

And my report results are:

Ticket Number Type BatchName CPT DOS Charge Entry Date
ASH001344 Office Visit EMR ASH BLUM 112310 90471 11/19/2010 11/25/2010
ASH001344 Office Visit EMR ASH BLUM 112310 90472 11/19/2010 11/23/2010
ASH001344 Office Visit EMR ASH BLUM 112310 90658 11/19/2010 11/27/2010
ASH001344 Office Visit EMR ASH BLUM 112310 90715 11/19/2010 11/23/2010
ASH001344 Office Visit EMR ASH BLUM 112310 99204 11/19/2010 11/23/2010

I’ve then used the following SQL expression called “EarliestDOE” to select the earliest date and move it into the detail of my report:

(select min("PatientVisitProcs"."DateOfEntry")
from PatientVisitProcs
Where "PatientVisitProcs"."PatientVisitId"="PatientVisit"."PatientVisitId")

However my report results are:

Ticket Number Type BatchName CPT DOS Charge Entry Date EarliestDOE
ASH001344 Office Visit EMR ASH BLUM 112310 90471 11/19/2010 11/25/2010 11/19/2010
ASH001344 Office Visit EMR ASH BLUM 112310 90472 11/19/2010 11/23/2010 11/19/2010
ASH001344 Office Visit EMR ASH BLUM 112310 90658 11/19/2010 11/27/2010 11/19/2010
ASH001344 Office Visit EMR ASH BLUM 112310 90715 11/19/2010 11/23/2010 11/19/2010
ASH001344 Office Visit EMR ASH BLUM 112310 99204 11/19/2010 11/23/2010 11/19/2010

The SQL Expression is pulling the date from the batch that did not meet my record selection criteria. How can I get the report to pull the earliest date from only the batches selected by the Record Selection formula.

Thank you

 

[Turkbear]

Hi,
Add the same selection criteria to the SQL expression - it is evaluated separately and does not use the CR criteria.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[GayleC]

I tried that but was having problems with Crystal accepting it. I'm new to SQL, so it's hit or miss for me. I keep getting an error message about either needing an Exist statement or that my datafield needs to be included in a grouping. Any additional tips would be appreciated.

 

[lbass]

How is the Batch table linked to your other table? On what field? I think you should describe the links in the main report or show a copy of your SQL query (database->show SQL query) so we can see the linking, etc.

-LB

 

[GayleC]

Here is my SQL Query, there are actually 5 tables involved in the entire report. The Batch table is linked on "Batch ID" to the table the contains the multiple lines of data. That table is called "PatientVisitProcs".

SELECT "PatientVisit"."TicketNumber", "PatientVisit"."Visit", "DOCTYPES"."DESCRIPTION", "DOCUMENT"."PUBTIME", "Batch"."Name", ((
select min("PatientVisitProcs"."DateOfEntry")
from PatientVisitProcs
Where "PatientVisitProcs"."PatientVisitId"="PatientVisit"."PatientVisitId"
))
FROM ((("CentricityPS"."dbo"."PatientVisitProcs" "PatientVisitProcs" INNER JOIN "CentricityPS"."dbo"."PatientVisit" "PatientVisit" ON "PatientVisitProcs"."PatientVisitId"="PatientVisit"."PatientVisitId") INNER JOIN "CentricityPS"."dbo"."Batch" "Batch" ON "PatientVisitProcs"."BatchId"="Batch"."BatchId") INNER JOIN "CentricityPS"."dbo"."DOCUMENT" "DOCUMENT" ON "PatientVisit"."PatientVisitId"="DOCUMENT"."PatientVisitId") INNER JOIN "CentricityPS"."dbo"."DOCTYPES" "DOCTYPES" ON "DOCUMENT"."DOCTYPE"="DOCTYPES"."DTID"
WHERE "Batch"."Name" LIKE 'EMR%'
ORDER BY "PatientVisit"."TicketNumber"

 

[lbass]

Try the following;

(
select min("PatientVisitProcs"."DateOfEntry")
from PatientVisitProcs, Batch
Where "PatientVisitProcs"."PatientVisitId"="PatientVisit"."PatientVisitId" and
"PatientVisitProcs"."BatchId"="Batch"."BatchId" and
"Batch"."Name" LIKE 'EMR%'
)

But in retrospect you could more simply just place a formula like this in the detail section, assuming a group on ticketnumber:

minimum({PatientVisitProcs.DateOfEntry},{PatientVisit.TicketNumber})

-LB

 

[GayleC]

The first option work, the 2nd one still pulled from the batch I was excluding.

Thank you so much for your help, I really appreciate it.

Gayle

 

[lbass]

Do you have the report working now? The minimum should have worked if you added the batch selection to your record selection formula.

-LB