Problem Description

The table tb_healthy records the medical history of patients; each patient has several records, where Visit_code = Surgery means an operation and Visit_code = Office means a consultation. Some patients have only operations, some have only consultations. Two questions have to be answered: whether the patient ever had a consultation, and whether the patient had a consultation within 6 months after an operation. The first goes into Office_Visit and the second into SX_past_6mo, with 1 for yes and 0 for no.

Source Data

tb_healthy table:

Expected Result

Office_Visit means the patient has consultation records, SX_past_6mo means the patient had a consultation within 6 months after an operation:

A30 had an operation on 2004-05-15 and a consultation on 2005-02-05, more than 8 months apart, beyond the 6-month window, so Office_Visit is 1 while SX_past_6mo is 0; this shows that "having a consultation" and "having a consultation within 6 months after an operation" are two different things.

C01 has two operation records and two consultation records, and only the operation of 2001-05-15 pairs with the consultation of 2001-11-01 inside the 6-month window, so SX_past_6mo is 1; C02 has only operations and C03 has only consultations, so both flags are 0 for them.

SQLazy Step-by-Step Implementation

This problem has to answer two questions at once, so it suits calculating the two things separately and merging them at the end. First convert the string dates into real dates, then filter out one copy of the operation records and one copy of the consultation records. "Has the patient ever had a consultation" comes from a summarize that groups by patient and takes the maximum. "Has the patient had a consultation within 6 months after an operation" needs the join action to attach consultation records to operation records, and the attaching condition is "the consultation date is not earlier than the operation date and not later than the operation date plus 6 months", which is a cross-table interval condition and exactly what the non-equi condition parameter of the join action is for. Summarizing the paired records gives the flag, and patients that never paired up are filled with 0 when the results are merged at the end.

[Click to run this example online]

The steps are explained below.

Step 1: Convert the string dates into date values

compute date(Visit_Date; format "M/d/yyyy") as dt

Visit_Date is a string such as "5/15/2004" and cannot be compared directly. Use the date function with the format "M/d/yyyy" to parse it into a date value and write it into the new column dt. This step is the prerequisite for every date comparison that follows.

Step 2: Filter one copy of the operation records and one of the consultation records
filter Visit_code=“Surgery”
filter Visit_code=“Office”
The two copies are used by different calculations later: the operation records serve as the base table of the join and the consultation records as the table being attached. The comparison uses the symbol “=”, not “==” as in SQL.

Step 3: Answer “has the patient ever had a consultation” first
summarize max if(Visit_code=“Office” then 1 else 0) as Office_Visit; group Id
The summarize action groups by Id and takes the maximum of the condition if(Visit_code=“Office” then 1 else 0) inside each group: a group with at least one consultation record yields 1, a group without any yields 0. The aggregation max is written before the aggregated item, and the result is named Office_Visit.

Step 4: Attach the consultations within 6 months to the operations
join Id; with t_off; Id; take dt as off_dt; inner; condition ((dt>=dt@) and (dt<=(dt@ elapse 6 month)))
This is the core step of the problem. The join action takes the operation table t_sx as the base table, joins the consultation table t_off on Id, and attaches the consultation date dt under the new name off_dt.
The condition is a cross-table interval test written in the condition parameter: dt@ with the @ suffix is the date of the base table (the operation), and dt without @ is the date of the joined table (the consultation), so the whole condition reads “the consultation date is not earlier than the operation date and not later than the operation date plus 6 months”. Moving 6 months forward uses the elapse function, written as (dt@ elapse 6 month).
Note: to reference a joined-table field in the condition, use its original name dt in that table, not the new name off_dt; the inner join drops the operation records that do not fit the window, so what remains are the paired records.

Step 5: Summarize the paired records into a flag
summarize max 1 as SX_past_6mo; group Id
Group by Id and take the maximum of the constant 1 inside each group. A patient that paired up at least once has at least one row and yields 1; a patient that never paired up does not appear in this table at all, which is exactly why 0 has to be filled in later. The aggregated item here is the constant 1 rather than a column, because the only information needed is whether a pair exists, not any particular value.

Step 6: Merge the two results
join Id; with t_sx_yes; Id; take SX_past_6mo
Using the result of step 3 as the base table, attach the flag from step 5 with a left join. The left join keeps the patients that never paired up in the result, with SX_past_6mo left empty. No join type is written here because the default is a left join.

Step 7: Replace the empty values with 0
compute nvl(SX_past_6mo,0) as SX_past
Use nvl to replace the empty values with 0 and name the column SX_past, so every flag in the output is either 0 or 1. The last step uses the derive action to pick the three columns that are needed: the patient Id, whether there was a consultation Office_Visit, and whether there was a consultation within 6 months after an operation SX_past.

The last step picks the columns to output.

Generated SQL

After confirming the logic of the above 7 steps, the SQLazy compiler automatically generates native SQL (Oracle syntax used here):

WITH t1 AS (
    SELECT Id, Visit_Date, Visit_code
      , TO_DATE(Visit_Date, 'M/d/yyyy') AS dt
    FROM tb_healthy
  ),
  t_results AS (
    SELECT Id
      , MAX(CASE
        WHEN Visit_code = 'Office' THEN 1
        ELSE 0
      END) AS Office_Visit
    FROM t1
    GROUP BY Id
  ),
  t_off AS (
    SELECT Id, Visit_Date, Visit_code, dt
    FROM t1
    WHERE Visit_code = 'Office'
  ),
  t_sx_yes AS (
    SELECT Id, MAX(1) AS SX_past_6mo
    FROM (
      SELECT t_sx.Id, t_sx.Visit_Date, t_sx.Visit_code, t_sx.dt, t_off.dt AS off_dt
      FROM t1
        INNER JOIN t_off
        ON t_sx.Id = t_off.Id
          AND ((t_off.dt >= t_sx.dt)
            AND (t_off.dt <= ADDMONTHS(t_sx.dt, 6)))
      WHERE t_sx.Visit_code = 'Surgery'
    ) t_2
    GROUP BY Id
  ),
  t_merged AS (
    SELECT t_results.Id, t_results.Office_Visit, t_sx_yes.SX_past_6mo
    FROM t_results
      LEFT JOIN t_sx_yes ON t_results.Id = t_sx_yes.Id
  ),
  t_final AS (
    SELECT Id, Office_Visit, SX_past_6mo
      , COALESCE(SX_past_6mo, 0) AS SX_past
    FROM t_merged
  )
SELECT Id, Office_Visit, SX_past
FROM t_final
ORDER BY Id

SQLazy lets you describe logic in business language instead of writing nested SQL queries. This problem has to answer both "has the patient ever had a consultation" and "was there a consultation within 6 months after an operation", and SQLazy splits them into two independent groups of steps that are worked out separately and merged at the end. The non-equi condition parameter of the join action is the key: "the consultation date falls within 6 months after the operation date" is a cross-table interval test, and SQLazy tells the two tables apart with the @ suffix so the interval condition can be written straight into the join statement instead of building a Cartesian product and filtering afterwards. The summarize action keeps "is there any" questions short, since a group maximum yields 0 or 1 without filtering records first and then testing for existence. Every step is an intermediate result table you can inspect on its own, so when something goes wrong it is immediately clear which step is at fault.

Official Links

SQLazy Online Experience: sqlazy.com (free, no registration required)

SQLazy Repository: github.com/SPLWare/SQLazy