Hi All, I have two SQL Statements that I am really hoping to combine to extract some infromation from out CRM package.
The first statement retreives a list of companies that we have not had contact with in the last 30 days, the second statement retreives a record that shows the last time an Engineer visited that company/client.
I was really hoping that I could nest the statments to combine the output, in other words Retreive the list of companies and have an accompanying colums that show the last person to visit and the date that occured.
I know a Union wont work here as the fields are different.
SELECT c.strCompanyName, c.ID
FROM tblCompany c
LEFT JOIN tblTimesheet a
ON c.ID = a.intClientID
AND a.dtWorkDate >= DATEADD(day, -30, getdate())
WHERE a.intClientID IS NULL
SELECT Top 1 tblCompany.ID, tblCompany.strCompanyName, tblTimeSheet.dtWorkDate,tblDomainAccountMap.strDomainUsername
FROM tblTimeSheet INNER JOIN
tblCompany ON tblTimeSheet.intClientID = tblCompany.ID INNER JOIN
tblDomainAccountMap ON tblTimeSheet.intEngineerID = tblDomainAccountMap.intUserID
Where tblCompany.ID=C.ID Order By dtWorkDate Desc