multiple rows per id to 1 id per row with conditions

22 Views Asked by At

I have a dataset, bene_id can be viewed as a patient. Each bene_id might have multiple rows or only one row as below. enter image description here

I want to make it as below table enter image description here

The criteria is if within the same bene_id if its rn=1 and day1<0 and without other row records (say rn=2 or even more), then keep that record. if within the same bene_id if its rn=1 and day1<0 but with rn=2 record, then use the second row (rn=2) if rn=1 and day1>=0 then use rn=1 record regardless rn =2 or else..

I use below SQL codes but it doesn't work well, it pull the rn=1 day1 <0 but it does not pull rn has =2 option and its correspond day1 >=0. Below is my codes. please advice.

CREATE TABLE A AS (
  SELECT
      bene_id,
      cancer_breast_ever,
      bcsv1date,
      rn,
      day1
  FROM (
      SELECT
          bene_id,
          cancer_breast_ever,
          bcsv1date,
          rn,
          day1,
          ROW_NUMBER() OVER (PARTITION BY bene_id ORDER BY 
              CASE 
                  WHEN rn = 1 AND day1 < 0 AND MIN(day1) < 0 AND MAX(rn) > 2 THEN 2
                  WHEN rn = 1 AND day1 < 0 THEN 1
                  WHEN rn = 1 AND day1 >= 0 THEN 1
                  ELSE 3
              END, 
              day1
          ) AS new_rn
      FROM
         B  f1
      GROUP BY
          bene_id, cancer_breast_ever, bcsv1date, rn, day1
  ) AS ranked_data_with_new_rn
  WHERE
      new_rn = 1`  
0

There are 0 best solutions below