Ineffective subquery SQL Server -

i have 2 tables, first table information customers, , second contains solds customers (clientes, albarán_cliente) can see structure here, , need obtain list of customers, column whith sales 2 years 1 year ago, , column sales since 1 year ago until today.

something this:

customers    sales_from_2_years_ago_to_1_year_ago      sales_from_1_year_ago_to ----------  ---------------------------------------     --------------------------- aaa                     1000                                    2000 bbb                     850                                     900 ccc                     20000                                   15000 

i can obtain subqueries:

select      c.nombre, (select sum(acc.importe)                 clientes cc                 join albaran_cliente acc on acc.codcli = cc.codcli                 fecalb > dateadd(month, -24, cast(getdate() date))                    , fecalb < dateadd(month, -12, cast(getdate() date))                   , cc.nombre = c.nombre                 group cc.nombre )[de -24 -12 meses],  (select sum(acc.importe) clientes cc join albaran_cliente acc on acc.codcli=cc.codcli  fecalb>dateadd(month,-12,cast(getdate() date)) ,  fecalb<cast(getdate() date) , cc.nombre=c.nombre group cc.nombre)[de -12 0 meses]  clientes c join albaran_cliente ac on ac.codcli=c.codcli  fecalb>dateadd(month,-24,cast(getdate() date))  group c.nombre 

but performance sucks,so tried options, read , tried using over(partition), not seem work me, tried use with :

with eq   ( select cc.nombre,sum(acc.importe)[suma1]  clientes cc join albaran_cliente acc on acc.codcli=cc.codcli fecalb>dateadd(month,-24,cast(getdate() date))  , fecalb<dateadd(month,-12,cast(getdate() date))   group cc.nombre   )  , eq2     (select cc.nombre,sum(acc.importe)[suma2]  clientes cc join albaran_cliente acc on acc.codcli=cc.codcli fecalb>dateadd(month,-12,cast(getdate() date))  , fecalb<cast(getdate() date)  group cc.nombre )  select c.nombre, suma1,suma2 clientes c join albaran_cliente ac on ac.codcli=c.codcli  left join eq on eq.nombre=c.nombre left join eq2 on eq2.nombre=c.nombre  fecalb>dateadd(month,-24,cast(getdate() date))  group c.nombre,suma1,suma2 

but takes many many more time(i've stopped him after long time)

so question is: is ther better way obtain result?

thanks.

yes. use conditional aggregation:

select cc.nombre,        sum(case when fecalb > dateadd(month, -24, cast(getdate() date)) ,                       fecalb < dateadd(month, -12, cast(getdate() date))                 acc.importe else 0            end) suma1,        sum(case when fecalb > dateadd(month, -12, cast(getdate() date)) ,                      fecalb < cast(getdate() date)                 acc.importe else 0            end) suma2 clientes cc join      albaran_cliente acc      on acc.codcli = cc.codcli   group cc.nombre;