You’re offline. This is a read only version of the page.
15
Hi to everyone!
I'm creating a report that should group the leads by Industry Area and then show how many leads have been created and how many have been qualified in the last seven days.
So far I have been able to perform the first two steps in Visual Studio, but I cannot find any field related to the lead's qualification time and date in the CRM db.
This is the query I wrote:
SELECT industrycodename AS IndustryName, COUNT(industrycode) AS CreatedLastWeek
FROM FilteredLead AS CRMAF_FilteredLead
WHERE createdon > DATEADD(day, -7, CONVERT(datetime, CONVERT(char(10), GETDATE(),
101))) and DATEPART(week, createdon) = DATEPART(week, GETDATE())
GROUP BY industrycodename;
How could I add a third column with the number of qualified leads per Industry Area in the last seven days?
*This post is locked for comments
I have the same question (0)I figured out I can use the modifiedon attribute to check when the Lead has been last modified and then if the value of statuscode is 3 or 4 the lead has been qualified or disqualified.
Combining the two fields it is possible to determine if the Lead has been qualified in the last week.
The resulting code is this:
SELECT IndustryName, CreatedLastWeek, QualifiedLastWeek
FROM ((SELECT industrycodename AS IndustryName, COUNT(industrycode) AS CreatedLastWeek
FROM FilteredLead AS CRMAF_FilteredLead
WHERE createdon > DATEADD(day, -7, CONVERT(datetime, CONVERT(char(10), GETDATE(),
101))) and DATEPART(week, createdon) = DATEPART(week, GETDATE())
GROUP BY industrycodename) AS First
FULL OUTER JOIN
(SELECT industrycodename AS IndustryName2, COUNT(industrycode) AS QualifiedLastWeek
FROM FilteredLead AS CRMAF_FilteredLead2
WHERE modifiedon > DATEADD(day, -7, CONVERT(datetime, CONVERT(char(10), GETDATE(),
101))) and DATEPART(week, modifiedon) = DATEPART(week, GETDATE()) and (statuscode = 3 or statuscode = 4)
GROUP BY industrycodename) AS Second
ON First.IndustryName = Second.IndustryName2)
I just started with CRMs so I am willing to hear second opinions on that!
Marco
Share
Report
All responses (
Answers (
