Filling in missing rows in a table

Question

At first my question may seem simple and has been asked before. Bear with me - I think it is a unique question.

Table A has columns State, County, Month, Year, and Rate. Each State,County composite is listed several times with different dates and rate. Some rows have State and County set and everything else in that row is NULL.

Table B has default rates for each month and year. Columns are Month, Year, and Rate. In this table I have several years worth of default data.

So for each State,County composite in Table A I want to fill in any missing data with the data from Table B.

I created a Table C that looks just like Table A except all the data is filled in with the default data from Table B. Then I tried to UNION Table A and Table C together. But I am ending up with two problems.

First I am ending up with duplicate rows with everything the same except for the rate. In this case I want to keep only the row that was originally in Table A (not the 'default rate').

Second I am ending up with rows rows that have State and County set but everything else is NULL. I need to replace these rows with row for every single default rate.

So in the end I want to have one row for each State,County,Month,Year composite.

Is it possible to combine the tables as I have described.

Let me know if you need anything clarified. Thanks.

Table A has several thousand rows. 1 to 48 rows for each State,County composite:

+-------+--------+-------+------+------+
| State | County | Month | Year | Rate |
+-------+--------+-------+------+------+
| NY    | Albany | 1     | 2011 | ###  |
| NY    | Albany | 2     | 2011 | ###  |
...
| NY    | Albany | 12    | 2011 | ###  |
| NY    | Albany | 1     | 2012 | ###  |
...
| NY    | Albany | 12    | 2012 | ###  |
| NY    | Monroe | 1     | 2011 | ###  |
...
| NY    | Monroe | 12    | 2011 | ###  |
| NY    | Essex  | NULL  | NULL | NULL |
+-------+--------+-------+------+------+

Table B has 36 rows. One row for each month over 3 years:

+-------+------+------+
| Month | Year | Rate |
+-------+------+------+
| 1     | 2011 | ***  |
| 2     | 2011 | ***  |
| ...   |      |      |
| 12    | 2011 | ***  |
| 1     | 2012 | ***  |
| ...   |      |      |
| 12    | 2012 | ***  |
| 1     | 2013 | ***  |
| ...   |      |      |
| 12    | 2013 | ***  |
+-------+------+------+

Resulting table has more rows than Table A. Each State,County composite has at lease the 36 rows from the default table:

+-------+--------+-------+------+------+
| State | County | Month | Year | Rate |
+-------+--------+-------+------+------+
| NY    | Albany |     1 | 2011 | ###  |
| ...   |        |       |      |      |
| NY    | Albany |    12 | 2011 | ###  |
| NY    | Albany |     1 | 2012 | ###  |
| ...   |        |       |      |      |
| NY    | Albany |    12 | 2012 | ###  |
| NY    | Albany |     1 | 2013 | ***  |
| ...   |        |       |      |      |
| NY    | Albany |    12 | 2013 | ***  |
| NY    | Monroe |     1 | 2011 | ###  |
| ...   |        |       |      |      |
| NY    | Monroe |    12 | 2011 | ###  |
| NY    | Monroe |     1 | 2012 | ***  |
| ...   |        |       |      |      |
| NY    | Monroe |    12 | 2012 | ***  |
| NY    | Monroe |     1 | 2013 | ***  |
| ...   |        |       |      |      |
| NY    | Monroe |    12 | 2013 | ***  |
| NY    | Essex  |     1 | 2011 | ***  |
| ...   |        |       |      |      |
| NY    | Essex  |    12 | 2011 | ***  |
| NY    | Essex  |     1 | 2012 | ***  |
| ...   |        |       |      |      |
| NY    | Essex  |    12 | 2012 | ***  |
| NY    | Essex  |     1 | 2013 | ***  |
| ...   |        |       |      |      |
| NY    | Essex  |    12 | 2013 | ***  |
+-------+--------+-------+------+------+

Key: *** is a rate from the default table. ### is a rate from the other table

Answer 1

I think the best approach is to generate all combinations of the geography and time. You can do this by taking the state and county from tablea and cross joining with the year and month from tableb. Then use left join to see if there is any value in tablea. If so, choose it. Otherwise, take the value from tableb:

select sc.state, sc.county, ym.year, ym.month, coalesce(a.rate, ym.rate) as rate
from (select distinct state, county from tablea) sc cross join
     tableb ym left outer join
     tablea a
     on a.state = sc.state and a.county = sc.county and
        a.year = ym.year and a.month = ym.month ;

+7chars