sql - Combining detail data with summed data -

i trying create sales performance analysis report company takes 2 sets of sales targets.

1. sales targets existing customers - identified account number target set each customer, each month. each customer assigned sales rep can calculate existing sales target total rep easily. stored in table of own.

2. sales targets new business - lump sum figure each sales representative of how new business must win in financial year. stored in table of own columns identify month, year, rep , giving amount.

my etl script creates/updates new table row each customer each month showing sales, targets , variance against target customer.

the problem having how handle new business these figures lump sum whole year. if link them i'll end target multiple of number of new customers , amounts when sum rather true total.

has got similar experience or project kind of aggregation has caused them issues?

so in table sales_targets have columns:

company, account code, target type, target, cal year, cal month, month end 

with data:

bwa  p001          large        40000   2013      7          2013-07-31 

in other_targets table have same information minus account code data has been generalized don't yet know accounts new. target type in table set "new".

danny

it sounds want accounts, actual , target each. then, want 2 more numbers: 1. "other target" sales rep 2. total number of new businesses sales rep got

in sql server, nest select sub-query inside outer select clause. , can nest count number of new customers. finally, can divide these two, average new-customer target sales rep assigned account.

it query below, see fiddle more detail on assumed structure.

select account_code, cal_yyyymm, act_sales, sales_rep  , coalesce((select sum(target) sales_targets st      st.account_code=a.account_code        , st.cal_yyyymm=a.cal_yyyymm       , st.sales_rep=a.sales_rep        ),0) specific_target   , coalesce((select sum(target) other_targets st      st.cal_yyyymm=a.cal_yyyymm       , st.sales_rep=a.sales_rep        ),0) salesman_other_target  , coalesce((select count(*) actual_sales a2      a2.cal_yyyymm=a.cal_yyyymm       , a2.sales_rep=a.sales_rep          , not exists               (select 1                sales_targets st2                st2.account_code=a2.account_code                   , st2.cal_yyyymm=a2.cal_yyyymm                  , st2.sales_rep=a2.sales_rep                 )    ),0) salesman_new_accounts   , coalesce((select sum(target) other_targets st      st.cal_yyyymm=a.cal_yyyymm       , st.sales_rep=a.sales_rep        ),0)  / coalesce((select count(*) actual_sales a2      a2.cal_yyyymm=a.cal_yyyymm       , a2.sales_rep=a.sales_rep          , not exists               (select 1                sales_targets st2                st2.account_code=a2.account_code                   , st2.cal_yyyymm=a2.cal_yyyymm                  , st2.sales_rep=a2.sales_rep                 )    ),0) sales_avg  actual_sales