Nulls in rows with three common value, fourth varies with one match

Question

My fist table TEMP1 has codes noting period when status starts.

+PRSNID | LVL2  |  LOC   |   initialQTRYR | STAT_IMPRT_VAR

*a      |  AA   |  CHI   |    0118        |     z

*b      |  AA   |  CHI   |    0318        |     z

*b      |  AA   |  LOS   |    0119        |     y

*c      |  AA   |  LOS   |    0119        |     z

*d      |  BB   |  CHI   |    0118        |     y

row count: 100

My second table TEMP2 has a column with codes from all periods EMPLID is present.

+PRSNID | LVL2  |  LOC  |    validQTRYR  

*a      |  AA   |  CHI  |      0118      

*a      |  AA   |  CHI  |      0218

*a      |  AA   |  CHI  |      0318     

*b      |  AA   |  CHI  |      0318

*b      |  AA   |  CHI  |      0418      

*b      |  AA   |  LOS  |      0119

*b      |  AA   |  LOS  |      0219

*c      |  AA   |  LOS  |      0119

*c      |  AA   |  LOS  |      0219      

*d      |  BB   |  CHI  |      0118

*d      |  BB   |  CHI  |      0218

*d      |  BB   |  CHI  |      0318

row count: 500

I'm left outer joining the two tables TEMP2 to TEMP1 and pulling back values joining on EMPLID, LVL2, LOC, QTRYR (required join condition). I'm getting nulls when the STAT_IMPRT_VAR is not there in TEMP2 joined with TEMP1.

I need the row count to match TEMP2.

+PRSNID | LVL2  |  LOC   |   drvdQTRYR | STAT_IMPRT_VAR

*a      |  AA   |  CHI   |    0118     |   z

*a      |  AA   |  CHI   |    0218     |  <null>

*a      |  AA   |  CHI   |    0318     |  <null>

*b      |  AA   |  CHI   |    0318     |   z

*b      |  AA   |  CHI   |    0418     |  <null> 

*b      |  AA   |  LOS   |    0119     |   y

*b      |  AA   |  LOS   |    0219     |  <null>

*c      |  AA   |  LOS   |    0119     |   z

*c      |  AA   |  LOS   |    0219     |  <null> 

*d      |  BB   |  CHI   |    0118     |   y

*d      |  BB   |  CHI   |    0218     |  <null>

*d      |  BB   |  CHI   |    0318     |  <null>

row count: 500

I need the table to actually look like this

+PRSNID |  LVL2 |   LOC  |    drvdQTRYR | STAT_IMPRT_VAR

*a      |  AA   |  CHI   |    0118      |   z

*a      |  AA   |  CHI   |    0218      |   z

*a      |  AA   |  CHI   |    0318      |   z

*b      |  AA   |  CHI   |    0318      |   z

*b      |  AA   |  CHI   |    0418      |   z 

*b      |  AA   |  LOS   |    0119      |   y

*b      |  AA   |  LOS   |    0219      |   y

*c      |  AA   |  LOS   |    0119      |   z

*c      |  AA   |  LOS   |    0219      |   z

*d      |  BB   |  CHI   |    0118      |   y

*d      |  BB   |  CHI   |    0218      |   y

*d      |  BB   |  CHI   |    0318      |   y

row count: 500

The validQTRYR less than the status that changes in TEMP1 should keep the sames status from initialQTRYR. Any help would be appreciated.

I've built several temp table and tried to inner join to get the STAT_IMPRT_VAR.

SELECT e.PRSNID, 
e.LVL2    
e.LOC 
CASE a.initialQTRYR  
 WHEN e.validQTRYR  THEN a.initialQTRYR  
 ELSE e.validQTRYR  
END drvdQTRYR,
e.STRM, 
a.STAT_IMPRT_VAR
FROM TEMP2 e
LEFT OUTER JOIN TEMP1 a
ON e.PRSNID= a.PRSNID
AND e.LOC = a.LOC 
AND e.LVL2    = a.LVL2    
AND e.validQTRYR  = a.initialQTRYR

I need the table to actually look like this

+PRSNID |  LVL2 |   LOC  |    drvdQTRYR | STAT_IMPRT_VAR

*a      |  AA   |  CHI   |    0118      |   z

*a      |  AA   |  CHI   |    0218      |   z

*a      |  AA   |  CHI   |    0318      |   z

*b      |  AA   |  CHI   |    0318      |   z

*b      |  AA   |  CHI   |    0418      |   z 

*b      |  AA   |  LOS   |    0119      |   y

*b      |  AA   |  LOS   |    0219      |   y

*c      |  AA   |  LOS   |    0119      |   z

*c      |  AA   |  LOS   |    0219      |   z

*d      |  BB   |  CHI   |    0118      |   y

*d      |  BB   |  CHI   |    0218      |   y

*d      |  BB   |  CHI   |    0318      |   y

Null values is the problem when there's not a match and when the status changes with an update in TEMP1 from z to y.

Please edit your question to clearly define your problem with the relevant informations. It's hard to understand as it is — Arnaud Claudel, Aug 18 '19 at 15:55

score 1 · Answer 1 · answered Aug 18 '19 at 16:23

You can try the following query where we have derived one column sum_rn for further grouping.

With your_query as
(SELECT e.PRSNID, 
        e.LVL2,   
        e.LOC,
        CASE a.initialQTRYR  
        WHEN e.validQTRYR  THEN a.initialQTRYR  
        ELSE e.validQTRYR  
        END drvdQTRYR,
        e.STRM, 
        a.STAT_IMPRT_VAR
        FROM TEMP2 e
        LEFT OUTER JOIN TEMP1 a
        ON e.PRSNID= a.PRSNID
        AND e.LOC = a.LOC 
        AND e.LVL2    = a.LVL2    
        AND e.validQTRYR  = a.initialQTRYR)

select prsn_id,
       lvl2,
       loc,
       drvdqtryr,
       Strm,
       max(STAT_IMPRT_VAR) 
       over (partition by prsn_id, sum_rn order by null) as STAT_IMPRT_VAR
       from
      (select t.prsn_id,
              t.lvl2,
              t.loc,
              t.drvdQTRYR,
              t.STRM, 
              t.STAT_IMPRT_VAR,
              Sum(case when t.STAT_IMPRT_VAR is not null then 1 end) 
              over (partition by t.prsn_id order by to_date(drvdqtryr,'mmyy')) as sum_rn
        From your_query)

Cheers!!

Barbaros Özhan · Answer 2 · 2019-08-18T17:21:10.040

You can use FIRST_VALUE() OVER (...) analytic function :

SELECT e.PRSNID,
       e.LVL2,    
       e.LOC, 
       CASE a.initialQTRYR  
           WHEN e.validQTRYR  THEN a.initialQTRYR  
           ELSE e.validQTRYR  
       END drvdQTRYR,
     --e.STRM, 
       FIRST_VALUE(a.STAT_IMPRT_VAR) OVER 
                               (PARTITION BY e.PRSNID ,e.LOC ORDER BY e.PRSNID,e.LOC DESC)
       AS STAT_IMPRT_VAR
  FROM TEMP2 e
  LEFT JOIN TEMP1 a
    ON e.PRSNID = a.PRSNID
   AND e.LOC = a.LOC 
   AND e.LVL2 = a.LVL2    
   AND e.validQTRYR = a.initialQTRYR;

Changing steps for a.STAT_IMPRT_VAR column should be grouped by e.PRSNID and e.LOC columns through PARTITION BY clause(PARTITION BY e.PRSNID ,e.LOC).

Demo

Nulls in rows with three common value, fourth varies with one match

2 Answers2