sql - Linking on 0 or more missing data fields update -

i have 2 tables, list of offices have , list of income apportion offices

create table #income(city varchar(50),office varchar(50),yearsbudget money) insert #income select 'london', null,     5000 union select 'paris',  null,     6000 union select null,    'sales',   7000 union select 'london','support',10000  create table #offices(city varchar(50),office varchar(50),ratio float) insert #offices select 'london','research lab'     ,.15 union select 'london','customer services',.45 union select 'london','sales'            ,.05 union select 'london','admin'            ,.19 union select 'london','support'          ,.17 union select 'paris' ,'sales'            ,.15 union select 'paris' ,'admin'            ,.45 union select 'paris' ,'support'          ,.05 union select 'madrid','sales'            ,.45 union select 'madrid','research lab'     ,.25  

for example, in 1 row of data #income have £5000 have apportion known london offices, in in row have £6000 need apportion known paris offices

this can achieved in following sql

select o.city,o.office, convert(money,i.yearsbudget/data.rsum*o.ratio) thisyearsbudget #income left join #offices o on i.city=o.city left join (select child.city,sum(child.ratio) rsum #offices child group child.city ) data on i.city=data.city  i.office null 

for apportioning of £7000 sales offices , £10000 london support office need further 2 select statements , further select statement sum , group results 3 apportioning views. can achieve in 1 simple view?

sample data:

create table income(city varchar(50),office varchar(50),yearsbudget money) insert income select 'london', null,     5000 union select 'paris',  null,     6000 union select null,    'sales',   7000 union select 'london','support',10000;  create table offices(city varchar(50),office varchar(50),ratio float) insert offices select 'london','research lab'     ,.15 union select 'london','customer services',.45 union select 'london','sales'            ,.05 union select 'london','admin'            ,.19 union select 'london','support'          ,.17 union select 'paris' ,'sales'            ,.15 union select 'paris' ,'admin'            ,.45 union select 'paris' ,'support'          ,.05 union select 'madrid','sales'            ,.45 union select 'madrid','research lab'     ,.25; 

query:

   select o.city,           o.office,           thisyearsbudget = sum(convert(money,sq.yearsbudget*o.ratio/sq.totalratio))      offices o      join (           select i.city, i.office, i.yearsbudget, totalratio = sum(o.ratio)             income             join offices o on isnull(i.city,o.city)=o.city                           , isnull(i.office,o.office)=o.office         group i.city, i.office, i.yearsbudget           ) sq on isnull(sq.city,o.city)=o.city               , isnull(sq.office,o.office)=o.office  group grouping sets ((o.city, o.office)                          --,() -- uncomment line see grand total                         )  order city, office; 

results:

|   city |            office | thisyearsbudget | ------------------------------------------------ | london |             admin |        940.5941 | | london | customer services |       2227.7228 | | london |      research lab |        742.5743 | | london |             sales |        785.9863 | | london |           support |      10841.5842 | | madrid |             sales |       4846.1538 | |  paris |             admin |       4153.8462 | |  paris |             sales |            3000 | |  paris |           support |        461.5385 | 

sql fiddle demo