Ion Ion - 3 years ago 108
SQL Question

Ineffective subquery SQL Server

I have 2 tables, the first is a table with information about customers, and the second contains the solds from customers (clientes, albarán_cliente) you can see the structure here, and what I need is to obtain a list of customers, another column whith sales for 2 years up to 1 year ago, and another column sales since 1 year ago until today.

Something like this:

Customers Sales_from_2_years_ago_to_1_year_ago sales_from_1_year_ago_to now
---------- --------------------------------------- ---------------------------
aaa 1000 2000
bbb 850 900
ccc 20000 15000


I can obtain this with subqueries:

select
c.nombre, (select sum(acc.importe)
from CLIENTES cc
join ALBARAN_CLIENTE acc on acc.codcli = cc.codcli
where fecalb > DATEADD(MONTH, -24, cast(getdate() as date))
and fecalb < DATEADD(MONTH, -12, cast(getdate() as date))
and cc.NOMBRE = c.NOMBRE
group by cc.NOMBRE )[de -24 a -12 meses],

(select sum(acc.importe) from CLIENTES cc join ALBARAN_CLIENTE acc on acc.codcli=cc.codcli
where fecalb>DATEADD(MONTH,-12,cast(getdate() as date)) and
fecalb<cast(getdate() as date) and cc.NOMBRE=c.NOMBRE group by cc.NOMBRE)[de -12 a 0 meses]

from CLIENTES c join ALBARAN_CLIENTE ac on ac.codcli=c.codcli
where fecalb>DATEADD(MONTH,-24,cast(getdate() as date))
group by c.NOMBRE


but the performance sucks,so i tried another options,
I read and tried using over(partition), But it does not seem to work for me, then i tried to use
WITH
:

WITH EQ AS


( select cc.nombre,sum(acc.importe)[suma1]
from CLIENTES cc join ALBARAN_CLIENTE acc on acc.codcli=cc.codcli
where fecalb>DATEADD(MONTH,-24,cast(getdate() as date))
and fecalb<DATEADD(MONTH,-12,cast(getdate() as date))
group by cc.NOMBRE
)

, EQ2 AS
(select cc.nombre,sum(acc.importe)[suma2]
from CLIENTES cc join ALBARAN_CLIENTE acc on acc.codcli=cc.codcli
where fecalb>DATEADD(MONTH,-12,cast(getdate() as date))
and fecalb<cast(getdate() as date) group by cc.NOMBRE
)

SELECT c.nombre, suma1,suma2
from 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
where fecalb>DATEADD(MONTH,-24,cast(getdate() as date))
group by c.NOMBRE,suma1,suma2


but it takes many many more time(I've stopped him after a long time)

so my question is: is ther a better way to obtain that result?

thanks.

Answer Source

Yes. Use conditional aggregation:

select cc.nombre,
       sum(case when fecalb > DATEADD(MONTH, -24, cast(getdate() as date)) and

                     fecalb < DATEADD(MONTH, -12, cast(getdate() as date))
                then acc.importe else 0
           end) as suma1,
       sum(case when fecalb > DATEADD(MONTH, -12, cast(getdate() as date)) and
                     fecalb < cast(getdate() as date)
                then acc.importe else 0
           end) as suma2
from CLIENTES cc join
     ALBARAN_CLIENTE acc
     on acc.codcli = cc.codcli  
group by cc.NOMBRE;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download