asked on

SQL Statment to SQL Expression in Crystal Reports

I have a Crystal Report which displays multiple diagnosis codes, each diagnosis code populates a row of data (see example1). I need 1 row for each patient and would like to create a formula for each diagnosis code by specifying the "line" for each code. Also, I need the row to display when there is a "null" line.

Currently I am getting:

Name Principal Diagnosis Other Diagnosis
Smith 725.xx
Smith 729.xx

It should look like:

Name Principal Diagnosis Other Diagnosis
Smith 725.xx 729.xx

Principal Diagnosis is specified in a formula by using "line" =1
Other Diagnosis = 2

I have tried grouping on the patient's encounter id and creating a SQL command.

The SQL Command continues to show multiple rows. I used this with a formula specifying the "line".

SELECT      hdx.HSP_ACCOUNT_ID AS hsp_acct,
      hdx.LINE AS dx_line,
      edg.REF_BILL_CODE AS dx_ref_bill_code,
edg.DX_NAME AS dx_name

FROM HSP_ACCT_DX_LIST hdx inner join
CLARITY_EDG edg ON hdx.DX_ID = edg.DX_ID

WHERE (hdx.LINE IS NULL) OR hdx.LINE >= 1

Would a SQL Expression work?
How do I write the SQL Expression based on the SQL Statement above?

Mike McCracken

A SQL expresion can only return 1 value.

Have you considered a Cross tab?

mlmcc

jul_to_cool

ASKER

I only need to return REF_BILL_CODE. I thought that I could use the SQL Expression in conjuction with a formula for each diagnosis field.

edtechdba

How about using a sub-query in the SELECT statement? Not sure where you'd want it located in the SELECT statement, but I created an example below.

SELECT hdx.HSP_ACCOUNT_ID AS hsp_acct,  hdx.LINE AS dx_line, 
   edg.REF_BILL_CODE AS dx_ref_bill_code, 
   edg.DX_NAME AS dx_name,

   (SELECT TOP 1 a.<OtherDiagnosis> -- column name for other diagnosis
     FROM HSP_ACCT_DX_LIST a
     WHERE a.DX_ID = hdx.DX_ID
         AND a.LINE = hdx.LINE) As OtherDiagnosis
            
FROM HSP_ACCT_DX_LIST  hdx 
INNER JOIN CLARITY_EDG edg 
   ON hdx.DX_ID = edg.DX_ID
                      
WHERE    (hdx.LINE IS NULL) OR hdx.LINE >= 1

Open in new window

Kurt Reinhardt

Working with Epic Clarity can be challenging!

My first concern is that your command doesn't seem to have any of the other fields you need, such as patient names, etc... Does this mean you're trying to join a command to Clarity tables? If so, that's going to cause you major performance issues. You shouldn't ever link commands to other objects for data retrieval.

If you're simply using tables for the base information, such as MRN, PAT_ENC_CSN_ID, Patient Name, etc.... then you could use SQL Expressions as correlated subqueries to return the principal and secondary diagnoses and names. You can, if you're not using Oracle as your database (I generally see Epic Clarity on SQL Server and Oracle - I do work for two different hospitals and reports I write for one won't work for the other).

Since you're experimenting with a command, why not try something like the following:

SELECT
	...,
	DX1.REF_BILL_CODE AS DX1,
	DX1.DX_NAME AS DX1_NAME,
	DX2.REF_BILL_CODE AS DX2,
	DX2.DX_NAME AS DX2_NAME,
	...
FROM
	HSP_ACCOUNT HA
	--Returns a primary dx and dx name if it exists
	LEFT OUTER JOIN (
			SELECT
				Y.REF_BILL_CODE,
				Y.DX_NAME
			FROM
				HPS_ACCT_DX_LIST X
				INNER JOIN CLARITY_EDG Y ON X.DX_ID = Y.DX_ID
			WHERE
				HA.HSP_ACCOUNT_ID = X.HSP_ACCOUNT_ID
				X.LINE = 1
			) AS DX1
	--Returns a secondary dx and dx name if it exists
	LEFT OUTER JOIN (
			SELECT
				Y.REF_BILL_CODE,
				Y.DX_NAME
			FROM
				HPS_ACCT_DX_LIST X
				INNER JOIN CLARITY_EDG Y ON X.DX_ID = Y.DX_ID
			WHERE
				HA.HSP_ACCOUNT_ID = X.HSP_ACCOUNT_ID
				X.LINE = 2
			) AS DX2
WHERE
	...

Open in new window

This will let you join other tables, which will let you return the base recordset from tables, such as HSP_ACCOUNT and PATIENT, without inflating the number of rows returned due to multiple DX lines.

jul_to_cool

ASKER

There are 80+ fields and 12 Clarity tables (so far), this is going to be used for an extract. I have everything else working correctly except this piece. I don't really want (or have the time) to rewrite this monster as a command.

Thanks for your suggestions but I'm still stuck...Any other ideas?

Kurt Reinhardt

Are you using oracle or SQL server?

jul_to_cool

ASKER

SQL server

PortletPaul

You need to solve the at the SQL Server level, well before you start applying CR functions/features on top of that IMHO.

rhinok appears to understand that data model, so I suggest you take advantage of that knowledge and follow his advice.

a quick fix may be to use a "group by" (in sql not CR), with case expressions, like this:

SELECT
       hdx.HSP_ACCOUNT_ID AS hsp_acct
     , MAX(edg.REF_BILL_CODE) AS dx_ref_bill_code
     , MAX(CASE WHEN hdx.LINE = 1 THEN edg.DX_NAME ELSE NULL)) AS dx_name_principal
     , MAX(CASE WHEN hdx.LINE = 2 THEN edg.DX_NAME ELSE NULL)) AS dx_name_secondary
FROM HSP_ACCT_DX_LIST hdx
INNER JOIN CLARITY_EDG edg ON hdx.DX_ID = edg.DX_ID
WHERE (hdx.LINE IS NULL)
     OR hdx.LINE IN (1,2)
GROUP BY
       hdx.HSP_ACCOUNT_ID AS hsp_acct

Open in new window

NOTE each field such as edg.REF_BILL_CODE has to be treated like I have suggested here - and this may not be what you expect.

ASKER CERTIFIED SOLUTION

Kurt Reinhardt

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

jul_to_cool

ASKER

You totally ROCK!!!!

I did something like this on an OpTime report but it's been a looong time! If I didn't pick up the extract in the middle of creation (and I didn't have such a short deadline) then I would have re-created it from a SQL Command in the beginning.

You saved me from some late nights this week...thanks again!