Post reply

Selecting out specific data

  • 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!

  • January 21, 2010 at 7:51 am

    #1106707

    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!

  • January 21, 2010 at 8:01 am

    #1106718

    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]

  • January 21, 2010 at 8:37 am

    #1106742

    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?? 😉

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

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