Maludasek Maludasek - 4 months ago 11
SQL Question

Microsoft SQL Server query improve

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
(not required)?

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
Comments