How to get aggregate data on a join

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

General SQL Server Forums

New to SQL Server Programming

How to get aggregate data on a join

Author

Topic

amarundo
Starting Member

9 Posts

Posted - 2012-01-04 : 15:49:28

I'm pretty new to SQL, but not to programming in general.

Here's what I need:

Table A: customerid, customername
Table B: customerid, calldatetime, callresultcode
Table B will have many entries for every customerid, for everytime we called, each one with its own calldatetime and callresultcode.

I want to have first, a list of just the most recent calls to each customerid in Table B, and second, an aggregate of callresultcode, so it will say for callresultcode = 'Signed Up' 25 records, for callresultcode = 'Go fly a kite' 100 records, and so on. I'm only interested in the last callresultcode, because previous ones will have things like 'No Answer', 'Not home', etc. And we don't have that last result in Table A.

Thanks

pduffin
Yak Posting Veteran

68 Posts

Posted - 2012-01-04 : 17:43:26

create table #tmpCustomer (customerid int, customername char(50))
create table #tmpCustomerCall (customerid int, calldatetime datetime, callresultcode int)

insert into #tmpCustomer values (1,'bob')
insert into #tmpCustomer values (2,'jeff')

insert into #tmpCustomerCall values (1,'jan 01,11',100)
insert into #tmpCustomerCall values (1,'jan 05,11',101)
insert into #tmpCustomerCall values (1,'feb 05,11',101)

insert into #tmpCustomerCall values (2,'feb 01,11',100)
insert into #tmpCustomerCall values (2,'mar 05,11',101)
insert into #tmpCustomerCall values (2,'apr 05,11',103)

select cu.customerid,customername,calldatetime,callresultcode
from #tmpcustomer cu
join #tmpcustomercall ca on ca.customerid = cu.customerid
where ca.calldatetime =
(
select max(calldatetime) calldatetime
from #tmpcustomercall cc
where cc.customerid = cu.customerid
)

select callresultcode,count(*)
from
(
select cu.customerid,customername,calldatetime,callresultcode
from #tmpcustomer cu
join #tmpcustomercall ca on ca.customerid = cu.customerid
where ca.calldatetime =
(
select max(calldatetime) calldatetime
from #tmpcustomercall cc
where cc.customerid = cu.customerid
)
) a
group by callresultcode

Likes to run, hates the runs!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-05 : 10:24:14

sounds like this


SELECT t.customername,b.calldatetime,b.callresultcode,t2.Cnt
FROM tableA t
INNER JOIN tableB b
ON b.customerid = t.customerid
INNER JOIN (SELECT customerid,MAX(calldatetime) AS Latest
            FROM TableB
            GROUP BY customerid
            )t1
ON t1.customerid = b.customerid
AND t1.Latest = b.calldatetime
INNER JOIN (SELECT customerid,callresultcode,COUNT(*) AS Cnt
            FROM TableB
            GROUP BY customerid,callresultcode
            )t2
ON t2.customerid = t1.customerid
AND t2.callresultcode = b.callresultcode