Hello fellow partners,
I have created a user view in Sage CRM on the company entity.
I need a list of companies that do not have any future communications so we can follow up with them. I think the view works but it is very slow and to go from 1 page of data to the other it is taking 4 minutes. Can you please tell me what I am doing wrong or if there is a better way of doing this or optimizing this query?
Thank you
CREATE VIEW vNoCommCurrent
AS
SELECT
DISTINCT vCompany.Comp_Name,
vCompany.Comp_CompanyId
FROM dbo.vCompany
INNER JOIN dbo.vCommunication ON dbo.vCommunication.CmLi_Comm_CompanyID = dbo.vCompany.Comp_CompanyId
WHERE dbo.vCommunication.CmLi_Comm_CompanyID is not null and dbo.vCompany.Comp_Deleted is null
and dbo.vCompany.comp_type IN ('CUSTOMER','LEAD') AND dbo.vCompany.Comp_Status = 'ACTIVE' AND dbo.vCompany.comp_grptype IN ('CUST','LEAD') AND
NOT EXISTS
(SELECT dbo.vCommunication.comm_datetime FROM dbo.vCommunication WHERE dbo.vCommunication.comm_datetime >= CONVERT(VARCHAR(11),GETDATE(),106) AND dbo.vCommunication.CmLi_Comm_CompanyID = dbo.vCompany.Comp_CompanyId)
