Return Top record by date joined to Customer table

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

Transact-SQL (2005)

Return Top record by date joined to Customer table

Author

Topic

kwalter
Starting Member

2 Posts

Posted - 2010-06-10 : 11:44:03

I am writing a query to extract customer data for AR. They want a customer listing with, among other things, the most recent payment date. payments are recorded in the CashHead table. I need to outer-join the Customer table to the CashHead table on CustNum, but only return the most recent CashHead record, just one record, most recent. Here is what I have - any help would be appreciated!


SELECT   customer.custnum, 
customer.ParentCustNum,
customer.name AS CustomerName, 
customer.BTAddress1, 
customer.BTAddress2, 
customer.BTAddress3,
customer.BTCity,
customer.BTState,
customer.BTZip,
customer.BTCountry,
custcnt.name AS ContactName,
custcnt.ContactTitle,
customer.BTPhoneNum,
customer.BTFaxNum,
customer.CreditLimit,
salesrep.Name


FROM  customer 
		INNER JOIN custcnt ON customer.primbcon = custcnt.connum
		INNER JOIN salesrep ON customer.salesrepcode = salesrep.salesrepcode

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-06-11 : 09:19:16

To the Above Query you have mentioned, i have add the Query to get the Last Payment Paid from CashHead assuming that the table contain a Date Column and the Amount
-------------------------------------------------------------------
SELECT customer.custnum,
customer.ParentCustNum,
customer.name AS CustomerName,
customer.BTAddress1,
customer.BTAddress2,
customer.BTAddress3,
customer.BTCity,
customer.BTState,
customer.BTZip,
customer.BTCountry,
custcnt.name AS ContactName,
custcnt.ContactTitle,
customer.BTPhoneNum,
customer.BTFaxNum,
customer.CreditLimit,
salesrep.Name ,

(Select Max(PaymentDate) AS LastPaymentDate , Sum(Cash) as LastCashPaid
From CashHead Csh WHERE Csh.CustNum = customer.custnum)

FROM customer
INNER JOIN custcnt ON customer.primbcon = custcnt.connum
INNER JOIN salesrep ON customer.salesrepcode = salesrep.salesrepcode

-------------------------------------------------------
I have not tested the query
let me know if this helps you.

Lets unLearn

kwalter
Starting Member

2 Posts

Posted - 2010-06-11 : 09:35:17

Thanks for the feedback. I actually used a similar approach, but with a TOP statement like this:


SELECT   
customer.custnum, 
customer.ParentCustNum,
customer.name AS CustomerName, 
customer.BTAddress1, 
customer.BTAddress2, 
customer.BTAddress3,
customer.BTCity,
customer.BTState,
customer.BTZip,
customer.BTCountry,
custcnt.name AS ContactName,
custcnt.ContactTitle,
customer.BTPhoneNum,
customer.BTFaxNum,
customer.CreditLimit,
salesrep.Name,
(SELECT TOP 1
trandate
FROM cashhead
WHERE customer.custnum = cashhead.custnum AND cashhead.trantype = 'PayInv'
ORDER BY cashhead.trandate DESC) AS LastPaymentDate,



FROM  customer 
		LEFT OUTER JOIN custcnt ON customer.primbcon = custcnt.connum AND customer.custnum = custcnt.custnum
		LEFT OUTER JOIN salesrep ON customer.salesrepcode = salesrep.salesrepcode

Thanks for the help, though

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources