SQL Query for Crystal Reports

Question

I'm having difficulty getting information from an SQL database via Crystal reports. Because I can't get the SQL query to return the data I need, I'm looking at constructing the query directly instead of having Crystal Reports construct it.

The tables I am working with look something like this:


    Table: system.view_episode_summary_current
    +------------+----------------+------------+----------------+------------------------+
    | patient_id | episode_number | admit_date | discharge_date | attending_practitioner |
    +------------+----------------+------------+----------------+------------------------+
    | 111        | 4              | 01/05/2017 |                | 4444                   |
    +------------+----------------+------------+----------------+------------------------+
    | 222        | 8              | 03/17/2017 |                | 5555                   |
    +------------+----------------+------------+----------------+------------------------+
    | 222        | 9              | 03/20/2017 |                | 6666                   |
    +------------+----------------+------------+----------------+------------------------+
    | 333        | 2              | 10/08/2017 |                |                        |
    +------------+----------------+------------+----------------+------------------------+

    Table: system.user_practitioner_assignment
    +------------+----------------+---------------------+--------------------+
    | patient_id | episode_number | backup_practitioner | date_of_assignment |
    +------------+----------------+---------------------+--------------------+
    | 111        | 4              |                     |                    |
    +------------+----------------+---------------------+--------------------+
    | 222        | 8              | 7777                | 03/17/2017         |
    +------------+----------------+---------------------+--------------------+
    | 222        | 8              | 4444                | 05/18/2017         |
    +------------+----------------+---------------------+--------------------+
    | 222        | 9              |                     |                    |
    +------------+----------------+---------------------+--------------------+
    | 333        | 2              | 4444                | 10/08/2017         |
    +------------+----------------+---------------------+--------------------+
    | 333        | 2              | 5555                | 10/19/2017         |
    +------------+----------------+---------------------+--------------------+

I need the SQL query to return the patient_id, episode_number, and admit_date from the system.view_episode_summary_current table whenever staff X is the attending_practitioner or the most recently assigned backup_practitioner.

I’ve been able to get Crystal Reports to return either this:


    +-------------+------------+----------------+------------+
    | {?Staff_ID} | patient_id | episode_number | admit_date |
    +-------------+------------+----------------+------------+
    | 4444        | 111        | 4              | 01/05/2017 |
    +-------------+------------+----------------+------------+

Or this:


    +-------------+------------+----------------+------------+
    | {?Staff_ID} | patient_id | episode_number | admit_date |
    +-------------+------------+----------------+------------+
    | 4444        | 222        | 8              | 03/17/2017 |
    +-------------+------------+----------------+------------+

But not this, which are the results I need:


    +-------------+------------+----------------+------------+
    | {?Staff_ID} | patient_id | episode_number | admit_date |
    +-------------+------------+----------------+------------+
    | 4444        | 111        | 4              | 01/05/2017 |
    +-------------+------------+----------------+------------+
    | 4444        | 222        | 8              | 03/17/2017 |
    +-------------+------------+----------------+------------+

I can't figure out how to get the Crystal Reports OR statement to work right, and I'm not sure if I'm using the correct type of JOIN. This is why I’m looking at a direct SQL query command instead of having Crystal Reports construct the SQL query.

UPDATE:

So I've tried several things, but without success.

First, the SQL handler was being fickle about the aliases in the query, so I removed them. (I think I can use aliases if the alias name is at least 3 characters long, but I need to test it out some more.)

Next, it threw and error on the NULL clauses, so I tweaked them till it got past them.

Here is what the query currently looks like:

SELECT * 
  FROM (SELECT system.view_episode_summary_current.patient_id, 
               system.view_episode_summary_current.episode_number, 
               system.view_episode_summary_current.admit_date,
               system.view_episode_summary_current.attending_practitioner,
               CASE WHEN (system.user_practitioner_assignment.date_of_assignment IS NOT NULL)
                     AND (LAG(system.user_practitioner_assignment.date_of_assignment) OVER
                             ( PARTITION BY system.user_practitioner_assignment.patient_id
                                   ORDER BY system.user_practitioner_assignment.date_of_assignment
                             ) IS NOT NULL) THEN 
                             LAG(system.user_practitioner_assignment.backup_practitioner) OVER
                                 ( PARTITION BY system.user_practitioner_assignment.patient_id
                                       ORDER BY system.user_practitioner_assignment.date_of_assignment
                                 ) 
                END AS Last_Backup
        FROM system.view_episode_summary_current
       INNER -- WRITE JOINS LIKE THIS!!!
        JOIN system.user_practitioner_assignment
          ON system.view_episode_summary_current.patient_id = system.user_practitioner_assignment.patient_id
         AND system.view_episode_summary_current.episode_number = system.user_practitioner_assignment.episode_number
       ) TMP
 WHERE TMP.attending_practitioner = '4444'
    OR TMP.last_backup = '4444';

Now, it is throwing an error on the OVER clause. I'm assuming that means it is old enough that it doesn't know how to handle SQL window functions. So now I'm trying to figure how out re-write the LAG stuff using a self join and MAX() clause.

I'm brand new to SQL. For example, I'm assuming that capitalization in the SQL query is (mostly) irrelevant, based on what I've observed, but I'm not sure. I have been scouring the internet for help, but haven't found anything helpful that I can make work or make sense of (most of it feels like a foreign language at this point). Those I know both inside and outside of work that know SQL are stumped but this. I'm way out of my depth here, so any help would be appreciated.

So the part I am particularly stumped on is the backup_practitioner. If the most recently assigned backup_practitioner = {?StaffID} then I need the data. If it is not or is null, then I don't need the data. I also need the data whenever attending_practitioner = {?StaffID}. It seems like some version of max(date_of_assignment) should work for getting to the most recently assigned backup_practitioner if one exists, but that part is where everything falls apart. — jbh006

Error_2646 Error_2646 · Accepted Answer · 2018-10-15T20:30:41

You should use explicit join syntax (see bolded).

But, something like this might do the trick:

SELECT * 
  FROM (SELECT a.patient_id, 
               a.episode_number, 
               a.admit_date,
               a.attending_practitioner,
               CASE WHEN B.date_of_assignment IS NOT NULL
                     AND LAG(B.date_of_assignment) OVER
                             ( PARTITION BY B.patient_id
                                   ORDER BY B.date_of_assignment
                             ) IS NOT NULL THEN 
                             LAG(B.backup_practitioner) OVER
                                 ( PARTITION BY B.patient_id
                                       ORDER BY B.date_of_assignment
                                 ) 
                END AS Last_Backup
        FROM system.view_episode_summary_current a
       INNER -- WRITE JOINS LIKE THIS!!!
        JOIN system.user_practitioner_assignment b
          ON a.patient_id = b.patient_id
         AND a.episode_number = b.episode_number
       ) TMP
 WHERE TMP.attending_practitioner = '4444'
    OR TMP.last_backup = '4444';

SQL Query for Crystal Reports

1 Answers