Selecting out specific data

Question

Post reply

Selecting out specific data

Mike Menser

Hall of Fame

Points: 3679
More actions
January 21, 2010 at 7:42 am

#214782

I have 2 tables. I am selecting out some columns from each. Very simple. There is an account table, and a payment table. I selecting out company info, and then a payment date. It is returning 1 row for each payment entry. I ONLY want 1 row returned for each account, with the most recent payment date. I just am drawing a blank. Haven't had my coffee yet.
Also, I would rather not select out into a temp table, because I am using this code for a sql report. Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you definitely need some coffee; god knows i've drawn blanks like that before my daily caffiene infusion.

if you need the max(payment date) per company, you simply need a group by;

something like:

select [company info], sum(payments) ,max([payment date]) from sometable group by [company info]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Something like this ?

with cteAccounts

as

(

Select AcctNo,Payment,Date,row_number() over (partition by AcctNo order by Date desc) as Rown

from Accounts

)

select * from cteAccounts

where RowN = 1

Clear Sky SQL
My Blog[/url]

Mike Menser Hall of Fame Points: 3679 More actions · Answer 3

Thanks for the replies. I guess I should explain the entire situation. I tried the first solution, and it didn't give me duplicate rows on the company, but it only returned a few rows. It didnt return a record for all of the application companies. There is a variable that the user passes in, which is year. This is what it looks like as of now:

SELECT ab.Name, aeb.New_MembershipType, MAX(ieb.New_PaidDate) AS paiddate, SUM(ieb.New_PaidAmount) AS paidamt

FROM AccountExtensionBase aeb INNER JOIN

InvoiceBase ib ON ib.AccountId = aeb.AccountId INNER JOIN

InvoiceExtensionBase ieb ON ieb.InvoiceId = ib.InvoiceId INNER JOIN

AccountBase ab ON ab.AccountId = aeb.AccountId

WHERE (ib.Name LIKE '%membership%') AND (aeb.New_MembershipType = 1) AND (ab.StateCode = 0)

GROUP BY ab.Name

HAVING (MAX(DATEPART(year, ieb.New_PaidDate)) < @year)

I tried formatting the code to look like the second suggestion, but returned an error on the partition.

with topaccounts

as

(select ab.name, aeb.new_membershiptype, ieb.new_paiddate, row_number() over (partition by ab.accountid order by new_paiddate) as rown

from accountextensionbase aeb INNER JOIN

invoicebase ib ON ib.accountid = aeb.accountid INNER JOIN

invoiceextensionbase ieb ON ieb.invoiceid = ib.invoiceid INNER JOIN

accountbase ab ON ab.accountid = aeb.accountid

where ib.name like '%membership%' and aeb.new_membershiptype = 1 and ab.statecode = 0

group by new_paiddate, aeb.accountid, ab.name, aeb.new_membershiptype

having MAX(datepart(year,new_paiddate))< 2008

order by new_paiddate desc)

select * from topaccounts

where rown = 1

Now that you know the secrets I was hiding, any ideas?? 😉