Help with Min()/Grouping 5

[MikeBinKC]

Hello, All.

I am developing some reports to be used with a company's phone call accounting and CRM systems. For discussion purposes, let me set this scenario: Each employee is assigned a number of leads, whom they are expected to call. They may call each lead a number of times. I want my report to display the employee, the lead, and the earliest (min) date that a call was made to that lead. Here is a table and sample data:

Create Table MyCallRecord
(EmployeeName varchar(10), LeadName varchar(10),
PhoneNumberCalled varchar(15),
DateCalled smalldatetime)

Insert Into MyCallRecord
Select 'Moe', 'Sneezy','816-555-1212', '7/17/2008'
Union All
Select 'Moe', 'Sneezy', '816-555-1212', '7/18/2008'
Union All
Select 'Moe', 'Grumpy', '913-555-5555', '7/22/2008'
Union All
Select 'Moe', 'Grumpy', '913-555-1111', '7/20/2008'
Union All
Select 'Larry', 'Dopey', '816-555-0000', '7/14/2008'
Union All
Select 'Shemp', 'Bashful', '417-555-8262', '7/1/2008'
Union All
Select 'Shemp', 'Bashful', '417-555-8262', '7/5/2008'

With this data, I would want to see

Moe    Sneezy     816-555-1212     7/17/2008
Moe    Grumpy     913-555-1111     7/20/2008
Larry  Dopey      816-555-0000     7/14/2008
Shemp  Bashful    417-555-8262     7/1/2008

I've tried various groupings in my select, but I always end up with 7 rows instead of 4.

Can you please help me solve this problem?

Thanks so much!

Mike

 

[chamilz]

try this...

select  a.EmployeeName,
		a.LeadName,
		a.PhoneNumberCalled,
		a.DateCalled
from MyCallRecord a
inner join ( 
             select EmployeeName,
		   LeadName,
		   min(DateCalled)DateCalled
	     from MyCallRecord
	     group by EmployeeName,LeadName
	    ) b
on a.EmployeeName = b.EmployeeName
and a.LeadName = b.LeadName
and a.DateCalled = b.DateCalled

 

[ethorn10]

Am I missing something? From what I read, this should work:

select EmployeeName
    , LeadName
    , PhoneNumberCalled
    , MIN(DateCalled)
from MyCallRecord
group by EmployeeName
    , LeadName
    , PhoneNumberCalled

 

[MikeBinKC]

ethorn10,

That *almost* works; however it doesn't account for the fact that the lead might have more than one phone number that the employee could call. Notice that Grumpy was called twice, with different numbers. I don't care what number was called; I just want the earliest. Your query returns

Larry	Dopey	816-555-0000	2008-07-14 00:00:00
Moe	Grumpy	913-555-1111	2008-07-20 00:00:00
Moe	Grumpy	913-555-5555	2008-07-22 00:00:00
Moe	Sneezy	816-555-1212	2008-07-17 00:00:00
Shemp	Bashful	417-555-8262	2008-07-01 00:00:00

 

[MikeBinKC]

Chamilz,

I got the result I was looking for; thank you.

This is a technique I'm not familiar with. Can you explain the join?

MIke

 

[ethorn10]

See...I knew I was overlooking something. That pesky multiple phone numbers part. Good work chamilz.

 

[MikeBinKC]

Yeah, ethorn 10...I tried to convince the sales manager to allow calls to only one number so the numbers would be right, but he wasn't buying it. LOL. Thanks for your help!

Now to study that join, as I've never used a join that way before. Any insight?

 

[ethorn10]

chamilz's query could be further simplified by

select  a.EmployeeName,
        a.LeadName,
        a.PhoneNumberCalled,
        a.DateCalled
from MyCallRecord a
inner join ( 
             select LeadName,
           min(DateCalled)DateCalled
         from MyCallRecord
         group by LeadName
        ) b
and a.LeadName = b.LeadName
and a.DateCalled = b.DateCalled

The idea is that you're getting the min (earliest) date for a call placed to a lead. You're then joining that back to the original set based on that date and lead.

 

[SQLSister]

The join is called a derived table. The idea is that you create a select stament that pulls just the minimum details to identify a record for records you are interested in (often using an aggregate function and a group by) then you join that to the original table to get the other details. The key is to get enough data to idnetify the record uniquely in the derived table.

When using derived tables (which are faster than subselects and often are used to replace them), one thing you must remember is that all fields must have a name (thats why min(DateCalled) is named DateCalled) and the derived table itself must have an alias (b inthis case).


"NOTHING is more important in a database than integrity." ESquared

 

[chamilz]

MikeBinKC,

I think SQLSister explained it clearly.

you can further read about derived table here..

http://www.sqlservercentral.com/articles/Performance+Tuning/derivedtablebasics/597/