mercredi 7 janvier 2015

Using JOIN and SUM in SQL-Server Create View without "DISTINCT"

I'm using SQL Server 2005.


I'm trying to make an index view based on a query of these tables joined:



Table Name: CallRecords

TN Carrier CallDate Calls
----------------------------------------------
5558675309 10 2014-12-30 3
5558675309 2 2014-12-30 1
5551457868 13 2014-12-30 14


TableName: PhoneNumbers

TN CustomerCode
----------------------------------------------
5558675309 205
5551457868 118

Here's my original query and create view:



--query
SELECT DISTINCT PN.CustomerCode,
CR.CallDate,
CR.Carrier,
Sum(CR.Calls) as SumCalls
FROM CallRecords CR
INNER JOIN PhoneNumbers PN ON PN.TN = CR.TN
GROUP BY PN.Customer,
CR.CallDate,
CR.Carrier

--create view
CREATE VIEW CallsByCustCodeByCarrierByDay
WITH SCHEMABINDING
AS
SELECT DISTINCT PN.CustomerCode,
CR.CallDate,
CR.Carrier,
Sum(CR.Calls) as SumCalls,
COUNT_BIG(*) AS CountLines
FROM CallRecords CR
INNER JOIN PhoneNumbers PN ON PN.TN = CR.TN
GROUP BY PN.Customer,
CR.CallDate,
CR.Carrier


I am able to create the view but when I try to create an index on the view I get this error:



Cannot create index on view "dbo.CallsByCustCodeByCarrierByDay" because it contains the DISTINCT keyword. Consider removing DISTINCT from the view or not indexing the view. Alternatively, consider replacing DISTINCT with GROUP BY or COUNT_BIG(*) to simulate DISTINCT on grouping columns.

But if I remove "DISTINCT" then my SUM result is incorrect. I think it's because it is multiplying the true sum by the number of times a CustomerCode shows up in the PhoneNumbers table.


Is there a way I can still use this query with an indexed view or am I out of luck?


Aucun commentaire:

Enregistrer un commentaire