Filling in missing rows in a table
My question may seem simple at first and has been asked before. Bear with me - I think this is a unique question.
Table A contains columns State
, County
, Month
, Year
and Rate
. Each state, county meeting is listed several times with different dates and rates. Some rows have a set of states and counties, and everything else in that row is NULL.
Table B has default rates for each month and year. Columns: Month
, Year
and Rate
. In this table, I have default data for several years.
So, for each state, the compound county in Table A, I want to fill in any missing data with the data from Table B.
I created table C which looks exactly like table A, except that all data is filled with the default data from table B. Then I tried to share table AION and table C. But I ran into two problems.
I first get duplicate rows with everything the same except for the bet. In this case, I only want to keep the row that was originally in table A (not the "default rate").
Second, I end up with strings of strings that have a State and a County set, but everything else is NULL. I need to replace these lines with a line for each default standard rate.
So in the end, I want to have one row for each state, county, month, year.
Is it possible to combine tables as I described.
Let me know if you need anything clarified. Thank.
Table A contains several thousand rows. 1 to 48 lines for each state, county composition:
+ ------- + -------- + ------- + ------ + ------ + | 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 for 3 years:
+ ------- + ------ + ------ + | Month | Year | Rate | + ------- + ------ + ------ + | 1 | 2011 | *** | | 2 | 2011 | *** | | ... | | | | 12 | 2011 | *** | | 1 | 2012 | *** | | ... | | | | 12 | 2012 | *** | | 1 | 2013 | *** | | ... | | | | 12 | 2013 | *** | + ------- + ------ + ------ +
The resulting table has more rows than Table A. Each State, County composite has 36 rows leased 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 the rate from the default table. ###
is the speed from another table
source to share
I think the best approach is to create all combinations of geography and time. You can do this by grabbing state
both county
from tablea
and crosshairs with year
and month
from tableb
. Then use left join
to see if there is any value in tablea
. If yes, select it. Otherwise take the value 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
source to share