CODEWITHSUNDEEP
sqlsql-serverperformance
Maludasek
Maludasek

Reputation: 117

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.

Upvotes: 2

Views: 65

Answers (2)

Hogan
Hogan

Reputation: 70513

Based on your example you want this

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

Upvotes: 2

Kostya
Kostya

Reputation: 1605

you could try to use pivot

select 
InvoiceAcc,
CustomerName,
[2016-1]  as [01],
[2016-2]  as [02],
[2016-3]  as [03],
[2016-4]  as [04],
[2016-5]  as [05],
[2016-6]  as [06],
[2016-7]  as [07],
[2016-8]  as [08],
[2016-9]  as [09],
[2016-10] as [10],
[2016-11] as [11],
[2016-12] as [12]
from (
select 
InvoiceAcc,
CustomerName,
[Month],
sum(SalesValue) SalesValue
from dbo.tblsales 
group by InvoiceAcc, CustomerName,[Month]) tb
pivot 
(
sum(SalesValue)
for [Month] in ([2016-1],[2016-2] ,[2016-3],[2016-4],[2016-5],[2016-6],[2016-7],[2016-8],[2016-9],[2016-10],[2016-11],[2016-12])
) pvt

Upvotes: 0

Related Questions