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.