troubleshooting Question

SQL Statment to SQL Expression in Crystal Reports

Avatar of jul_to_cool
jul_to_coolFlag for United States of America asked on
Crystal Reports
10 Comments1 Solution1885 ViewsLast Modified:
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?
Kurt Reinhardt
Sr. Business Intelligence Consultant/Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros