Maludasek - 8 months ago 23

SQL Question

Is there any chance to improve speed of the query below:

`select distinct`

a.InvoiceAcc,

(select top 1 b.CustomerName

from dbo.tblsales as b

where b.InvoiceAcc = a.InvoiceAcc),

(select sum(b.SalesValue)

from dbo.tblsales as b

where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-1') as [01],

(select sum(b.SalesValue)

from dbo.tblsales as b

where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-2') as [02],

(select sum(b.SalesValue)

from dbo.tblsales as b

where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-3') as [03],

(select sum(b.SalesValue)

from dbo.tblsales as b

where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-4') as [04],

(select sum(b.SalesValue)

from dbo.tblsales as b

where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-5') as [05],

(select sum(b.SalesValue) from dbo.tblsales as b where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-6') as [06],

(select sum(b.SalesValue) from dbo.tblsales as b where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-7') as [07],

(select sum(b.SalesValue) from dbo.tblsales as b where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-8') as [08],

(select sum(b.SalesValue) from dbo.tblsales as b where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-9') as [09],

(select sum(b.SalesValue) from dbo.tblsales as b where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-10') as [10],

(select sum(b.SalesValue) from dbo.tblsales as b where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-11') as [11],

(select sum(b.SalesValue) from dbo.tblsales as b where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-12') as [12]

from

dbo.tblsales as a

group by

a.InvoiceAcc, CustomerName

What would be the view in SQL Server with params month sent as 2016 and

`InvoiceAcc`

Thanks a lot.

Answer

Based on your example you want this

```
select
InvoiceAcc,
CustomerName,
sum(CASE WHEN Month = '2016-1' then salesvalue else o end) as [01],
sum(CASE WHEN Month = '2016-2' then salesvalue else o end) as [02],
sum(CASE WHEN Month = '2016-3' then salesvalue else o end) as [03],
sum(CASE WHEN Month = '2016-4' then salesvalue else o end) as [04],
sum(CASE WHEN Month = '2016-5' then salesvalue else o end) as [05],
sum(CASE WHEN Month = '2016-6' then salesvalue else o end) as [06],
sum(CASE WHEN Month = '2016-7' then salesvalue else o end) as [07],
sum(CASE WHEN Month = '2016-8' then salesvalue else o end) as [08],
sum(CASE WHEN Month = '2016-9' then salesvalue else o end) as [09],
sum(CASE WHEN Month = '2016-10' then salesvalue else o end) as [10],
sum(CASE WHEN Month = '2016-11' then salesvalue else o end) as [11],
sum(CASE WHEN Month = '2016-12' then salesvalue else o end) as [12]
from dbo.tblsales
group by InvoiceAcc, CustomerName
```

I do find the test (eg `Month = '2016-1'`

) to be strange -- I would expect a date field and a test like this `MONTH(invoiceDate) = 1`

I also think that using distinct in sql is often an indication of a poor query -- it is rare a correctly designed query from a well designed model needs distinct.

If you want to remove additional names the best way to do it is this:

```
select
InvoiceAcc,
CustomerName,
sum(CASE WHEN Month = '2016-1' then salesvalue else o end) as [01],
sum(CASE WHEN Month = '2016-2' then salesvalue else o end) as [02],
sum(CASE WHEN Month = '2016-3' then salesvalue else o end) as [03],
sum(CASE WHEN Month = '2016-4' then salesvalue else o end) as [04],
sum(CASE WHEN Month = '2016-5' then salesvalue else o end) as [05],
sum(CASE WHEN Month = '2016-6' then salesvalue else o end) as [06],
sum(CASE WHEN Month = '2016-7' then salesvalue else o end) as [07],
sum(CASE WHEN Month = '2016-8' then salesvalue else o end) as [08],
sum(CASE WHEN Month = '2016-9' then salesvalue else o end) as [09],
sum(CASE WHEN Month = '2016-10' then salesvalue else o end) as [10],
sum(CASE WHEN Month = '2016-11' then salesvalue else o end) as [11],
sum(CASE WHEN Month = '2016-12' then salesvalue else o end) as [12]
from (
SELECT InvoiceAcc, CustomerName, Month, salesvalue,
ROW_NUMBER() OVER (PARTITION BY InvoiceAcc ORDER BY CustomerName) AS RN
) x
WHERE RN=1
group by InvoiceAcc, CustomerName
```