Select values with a more recent year and month when both are stored in different columns in SQL Server

The setup in both tables is that year and month are in two seperate columns as numbers. I am trying to select the values from one table that do not exists in the other table (so are more recent date wise).

For example: Table1 has values from 2016 until today and Table2 only until end of 2018. So I want to select the values from table 1 for 2019, since those are not yet in table2.

What I have so far:

Select * FROM
Table1
WHERE YearNb > 
    (Select top 1 YearNb 
    from Table2
    order by YearNb desc);

This gives me all values from Table 1 that have a higher year than table2. Now I have the issue that adding months is not that easy.. I was thinking of maybe combining the two columns in a temp table to an actual date for comparison.

Answer

If I understand your question correctly, you may try with next approach. Just generate date values from your year and month columns:

SQL Server 2012+

SELECT *
FROM #Table1
WHERE DATEFROMPARTS(Year1, Month1, 1) > (SELECT MAX(DATEFROMPARTS(Year2, Month2, 1)) FROM #Table2)

Earlier versions:

SELECT *
FROM #Table1
WHERE (Year1 * 100 + Month1) > (SELECT MAX(Year2 * 100 + Month2) FROM #Table2)