Join tables and replalace NULLS

Database
Fan

Join tables and replalace NULLS

Hi,

Could you please help me to join Table 2 to table 2 without null values

Thank you!

======================

Table 1(trending table keeps all months)

MonthID  CusID  Amount

201301    1          100

201302    1          150

201303    1           0 

201304    1           0

201305    1          100

201306    1          150

201307    1          100

201308    1           50

table 2 (keep only history of changes)

MonthID CustID  Manager

201301    1            A

201304    1            B

201307     1           C

How I can join these tables to get result:

MonthID  CusID  Amount  Manager

201301    1          100         A

201302    1          150         A

201303    1           0             A

201304    1           0             B

201305    1          100          B

201306    1          150          B

201307    1          100          C

201308    1           50           C

Tags (1)
2 REPLIES
Senior Apprentice

Re: Join tables and replalace NULLS

Try this:

SELECT 
tab.*,
MIN(tab2.Manager)
OVER (PARTITION BY tab.CustId
ORDER BY tab.MonthID
RESET WHEN tab2.Manager IS NOT NULL
ROWS UNBOUNDED PRECEDING
)
FROM tab LEFT JOIN tab2
ON tab.CustID = tab2.CustID
AND tab.MonthID = tab2.MonthID
Fan

Re: Join tables and replalace NULLS

Thank you, it works!!!