Different record types on same report row question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Different record types on same report row question

Author

Topic

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-08-05 : 16:24:40

I am somewhat new to Reporting Services and I have a situation that I have not stumbled on before and am looking for guidance from the experts.

I have a table that has information on sales leads called Leads. It has the following relevant fields.

“Lead_ID” binary(8)
“Lead_Sales_Person” varchar(40)
“Lead_Status” varchar(10)
“Lead_Create_Date” datetime
“Lead_Closed_Date” datetime

Lead Status is either “Open” or “Closed”

Lead_Create_Date is only populated when a sales lead is generated

And Lead_Closed_Date is only populated when the lead has been closed

My dilemma is this: I am being requested to create a report with the following data:

For every Sales Person, Count(Open Leads), Avg(Days Open), Count(Closed Leads), Avg(Days to Close)

The SQL is easy (for me) if the report only dealt with only the Open Leads or only the Closed Leads but combining the two has me stumped.

Open Leads:

SELECT Lead_Sales_Person, COUNT(Lead_Status) AS [Open Leads], AVG(DATEDIFF(dd, Lead_Create_Date, GETDATE())) AS [Days Open]
FROM Leads
WHERE (Lead_Status = 'Open')
GROUP BY Lead_Sales_Person
ORDER BY Lead_Sales_Person

Closed Leads:

SELECT Lead_Sales_Person, COUNT(Lead_Status) AS [Closed Leads], AVG(DATEDIFF(dd, Lead_Create_Date, Lead_Closed_Date)) AS [Days to Close]
FROM Leads
WHERE (Lead_Status = 'Closed')
GROUP BY Lead_Sales_Person
ORDER BY Lead_Sales_Person

Is this something I can do in pure SQL or in the report definition??? Stored Procedure with Temp Tables???

Any and all help is appreciated.

Thanks in advance,
John

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-05 : 17:11:36

Try this:

select 
dt1.Lead_Sales_Person, 
dt1.[Open Leads], 
dt1.[Days Open], 
dt2.[Closed Leads], 
dt2.[Days to Close]
from
(
SELECT Lead_Sales_Person, COUNT(Lead_Status) AS [Open Leads], AVG(DATEDIFF(dd, Lead_Create_Date, GETDATE())) AS [Days Open]
FROM Leads 
WHERE (Lead_Status = 'Open') 
GROUP BY Lead_Sales_Person
)dt1

join
(
SELECT Lead_Sales_Person, COUNT(Lead_Status) AS [Closed Leads], AVG(DATEDIFF(dd, Lead_Create_Date, Lead_Closed_Date)) AS [Days to Close]
FROM Leads 
WHERE (Lead_Status = 'Closed') 
GROUP BY Lead_Sales_Person
)dt2
on dt1.Lead_Sales_Person = dt2.Lead_Sales_Person

ORDER BY Lead_Sales_Person

No, you're never too old to Yak'n'Roll if you're too young to die.

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-08-06 : 08:22:51

Thank you! For some reason when I kept trying this in the query builder I kept getting syntax errors.

I am new to the forums here but will definitely visit daily!

John

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-06 : 11:56:07

It is always a good idea to post the error messages.
To say: "getting syntax errors" isn't very helpful for us

But first you should try the statement in SSMS...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources