sql - Calculation within the subquery, substract an amount from a different table -

i have been working on query on day now.

from total column in first query (calls) below, need add or subtract amount number second table (callsincrease) months within date range (from startdate enddate skill equals skill in query).

i tried create field first amount subtract each month, use subtract total.

i tried different versions of the sub-queries below:

expr1: (   select amount    (     select callsincrease.skill, sum(callsincrease.amount) amount     callsincrease     group callsincrease.skill   )   [skill]=(select [skill] callsincrease)     , [callmonth] between (select [startdate] callsincrease)       , (select [enddate] callsincrease) )   expr1: (   select sum([%$##@_alias].amount) sumofamount   (     select callsincrease.skill, sum(callsincrease.amount) amount     callsincrease     group callsincrease.skill)  [%$##@_alias]     group [%$##@_alias].skill     having ((([%$##@_alias].skill)=forecast.skill)) ) 

the sql calls query:

select   cdate(dateserial(year([forecastdate]),month([forecastdate]),1)) forecastmonth,   forecast.skill,   sum(forecast.normalizeddata) total forecast group cdate(dateserial(year([forecastdate]),month([forecastdate]),1)),   forecast.skill having (((sum(forecast.normalizeddata)) not null)); 

calls query:

forecastmonth   skill               total 01-jan-08   generalist english      56541 01-jan-08   generalist spanish      868 01-jan-08   public safety english   26837 01-jan-08   public safety spanish   401 01-jan-08   total                   34584 01-jan-08   utilities english       48332 01-jan-08   utilities spanish       1605 01-feb-08   generalist english      52176 

calls increase table:

id  eventid             skill        startdate  enddate     amount 1   9/5/2008    generalist english  01-jan-08   31-aug-08   1800 3   7/10/2010   generalist english  01-jan-08   30-jun-10   -3000 4   12/1/2011   generalist english  01-jan-08   30-nov-11   2500 5   3/1/2013    generalist english  01-jan-08   28-feb-13   200 6   12/1/2011   generalist spanish  01-jan-08   30-nov-11   80 7   7/1/2010    public safety eng   01-jan-08   30-jun-10   -4700 8   3/10/2009   utilities english   01-jan-08   28-feb-09   7000 

i hope makes sense, appreciated.

i came following horrendous mess. there hope dbms's query optimizer can reasonable it, in part because avoids correlated subqueries. indeed, there hope dbms accept query more or less written.

select   monthlyforecast.month forecastmonth,   monthlyforecast.skill skill,   (monthlyforecast.total + coalesce(adjustment.amount, 0)) total     (select       cdate(dateserial(year([forecastdate]),month([forecastdate]),1)) month,       forecast.skill skill,       sum(forecast.normalizeddata) total     forecast     group [month], [skill]     having total not null   ) monthlyforecast    left outer join      (select         forecastmonths.month month,         callsincrease.skill skill,         sum(callsincrease.amount) amount               (select distinct           cdate(dateserial(year([forecastdate]),month([forecastdate]),1)) month           forecast         ) forecastmonths          join callsincrease           on forecastmonths.month             between callsincrease.startdate , callsincrease.enddate       group         [month], [skill]     ) adjustment      on monthlyforecast.month = adjustment.month       , monthlyforecast.skill = adjustment.skill ; 

for it's worth, need compare specific dates date range significant complicating factor, both designing query , running it. there other ways handle problem 1 i've used above, i'm doubtful of them yield simpler query.