July 4, 2010 at 11:18 pm
DECLARE @CustMaster
TABLE (
CustID VARCHAR(10) NOT NULL,
CustName VARCHAR(50) NULL,
CustPhone VARCHAR(10) NULL
);
DECLARE @CustCalls
TABLE (
CustID VARCHAR(10) NOT NULL,
ContactName VARCHAR(50) NULL,
ContactPhone VARCHAR(10) NULL,
CallDate DATETIME NULL
);
INSERT @CustMaster (CustID, CustName, CustPhone)
VALUES ('1', 'Customer1', '12345345');
INSERT @CustMaster (CustID, CustName, CustPhone)
VALUES ('2', 'Customer2', '12345787');
INSERT @CustMaster (CustID, CustName, CustPhone)
VALUES ('3', 'Customer3', '34234344');
INSERT @CustCalls (CustID, ContactName, ContactPhone, CallDate)
VALUES ('1', 'Name1', '23423434','2010-01-01');
INSERT @CustCalls (CustID, ContactName, ContactPhone, CallDate)
VALUES ('1', 'Name1', '45454545','2010-02-02');
INSERT @CustCalls (CustID, ContactName, ContactPhone, CallDate)
VALUES ('3', 'Name3', '34234344','2010-03-21');
SELECT a.CustID, a.CustName, a.CustPhone
from @CustMaster a
SELECT * FROM @CustCalls
I need to bring up the CustMsater table rows along with the latest phone number from CustCalls tables
(based on the last call). If there is no call, it can show null value.
The result will look like this...
CustIDCustNameCustPhoneContactPhone
1Customer11234534545454545
2Customer212345787null
3Customer33423434434234344
Can you show me how to fix this. Thanks in advance
July 5, 2010 at 12:14 am
One way that comes to my mind is:
select a.Custid ,a.CustName,a.CustPhone,a.ContactPhone from (
select row_number() over (partition by a.Custid order by calldate desc ) rwnm,a.Custid ,a.CustName,a.CustPhone,b.CallDate,b.ContactPhone
from @CustMaster a LEFT JOIN @CustCalls b ON a.CustID=b.CustID) a
where rwnm=1
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
July 5, 2010 at 10:24 pm
Thanks Suresh.
July 6, 2010 at 12:41 pm
This will give you what you want. The OUTER APPLY will out perform the row number solution if there are many calls per customer and there is an index on CallDate (which doesn't apply here since you are using table variables).
SELECT CM.CustName, CM.CustName, CM.CustPhone
, LC.ContactPhone, LC.CallDate
FROM @CustMaster CM
OUTER APPLY
(SELECT TOP 1 ContactPhone, CallDate
FROM @CustCalls CC
WHERE CC.CustID = CM.CustID
ORDER BY CallDate DESC
) LC -- Last Call
Todd Fifield
July 6, 2010 at 10:52 pm
Thank you Todd for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply