Help with an SQL Expression - Return field value where Datetime is Min or First datetime

[Former Member]

Hello,

I'm extracting results for a patient population.

I have 6 SQL expressions that pull back the first date for specific procedures or Medications being ordered.

These Expressions work perfect - One of the expressions return the first time a lab was ordered.

I'm using SQL that return

Min(OrderDatetime)

From OrderTable

Where

LABCode = XXX

I've now been ask to return the value of this lab result.

Question: How do I write an SQL expression that return the Lab value of the Min(OrderDatetime)

Example Data

PATID* LABID * OrderDatetime * ResultValue

Pat1*133 * 12/12/2013 11:00:pm * 77

Pat1*145 * 12/12/2013 11:45:pm * 45

Pat1*147 * 12/13/2013 12:10:AM * 78

I was to return 77

Thanks

Steve

[abhilash_kumar]

Hi Steve,

Just use something like this:

(

     Select "Table"."ResultValue" from Table

     where Min(OrderDatetime) = "Table"."OrderDateTime"

     AND LABCode = XXX

)

-Abhilash

[Former Member]

Here is what I came up with but I'm getting an error -

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <,>,>= or when the subquery is used as an expression.

Any thoughts on what I'm doing wrong?

(
SELECT LABRESULTS.ORDER_VALUE as LabResult
  FROM   LABRESULTS, ORDERS_PROD, PATIENT_HSP

  WHERE
"ORDERS_PROD"."CSN_ID" = "LABRESULTS"."CSN_ID" AND
"ORDERS_PROD"."ORDER_PROC_ID" = "LABRESULTS"."ORDER_PROC_ID" AND
 

ORDERS_PROD.ORDER_INST IN     (
                              SELECT Min(ORDERS_PROD.ORDER_INST)
                              FROM   ORDERS_PROD
                              WHERE "PATIENT_HSP"."CSN_ID" = "ORDER_PROD"."CSN_ID" AND
                              ("ORDERS_PROD"."ORDER_STATUS_C"<>4 OR
                              "ORDERS_PROD"."ORDER_STATUS_C"<>9) AND
                              "ORDERS_PROD"."FUTURE_OR_STAND" IS  NULL AND
                              "ORDERS_PROD"."REASON_FOR_CANC_C" IS  NULL AND
                              ("ORDERS_PROD"."PROC_CODE" = 'LAB7764' OR
                              "ORDERS_PROD"."PROC_CODE" = 'LAB7765' OR
                              "ORDERS_PROD"."PROC_CODE" = 'LAB7766' OR
                              "ORDERS_PROD"."PROC_CODE" = 'LAB7767')
                             )

[DellSC]

A SQL Expression can only return a single value.  You would probably be better off in this case writing a "Command" (SQL Select statement using the syntax for your database) to provide the data for your report than linking tables and using a SQL Expression.  Your report will perform better too.

Here are a few best practices for using commands:

1.  In general ALL of the data in your report should come from a single command.  The report will be slow if you join tables and a command or multiple commands because Crystal can't push the joins down to the database.  Instead it will pull ALL of the data into memory and filter/join it there.

2.  DO NOT use the Select Expert to filter your report.  Instead put the filter in the Where clause of the command.  If you do this, the report will be slow for the same reason as item 1.

3.  If you're using parameters in your report, you MUST create the parameters in the Command Editor.  Crystal can't read parameters from the main report when processing a command.  However, once you've created a parameter in the Command Editor, you can modify it in the Field Explorer of the report so that you can configure it to be dynamic, have limits, etc.

-Dell