Selecting only oldest date in a 2 table join

  • My first post here, not sure if this is the right forum but here goes....

    I have the following query that returns a list of all customer orders that have a total amount greater than 0, and orders them by name and then order date.

    select

    account0.name as 'name',

    ab.createdon as 'orderdate'

    from FilteredAccount as account0

    join FilteredSalesOrder as ab

    on (account0.accountid = ab.customerid and (ab.totalamount > 0))

    order by account0.name, ab.createdon asc

    sample output:

    name Orderdate

    cust1 2000/01/01 06:00:00:000

    cust2 2004/10/01 08:44:00:000

    cust2 2005/03/01 06:00:00:000

    cust3 2000/01/01 06:00:00:000

    cust3 2002/01/01 06:00:00:000

    cust4 2000/01/01 06:00:00:000

    What I'm trying to do is only show the oldest order for each customer, for example I would like the sample output above to show:

    name Orderdate

    cust1 2000/01/01 06:00:00:000

    cust2 2004/10/01 08:44:00:000

    cust3 2000/01/01 06:00:00:000

    cust4 2000/01/01 06:00:00:000

    I'm pretty novice, any help or direction is greatly appreciated.

  • If you would post your table definitions some sample data (not anything that would divulge company confidential info), desired result from the sample data. To learn how to do all this please refer to the first link in my signature block.

    With the above, I am sure some one will assist you with a tested answer.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Hey Ron, the tables I'm working with are rather large (well over 100 columns each), I'm not sure how to show them to you accurately.

    The query in my first post returns just under 16,000 records, but I can't figure out how to have it return just the oldest order for each account, instead of all orders for each account.

    Basically one of the tables is an Accounts table, the other is an Orders table. I'm pulling only the accounts on the accounts table who have an order in the system that is greater than $0, and displaying all those orders sorting first by account, and then by the date the order was created on. I want to take this query that pulls this list of 'first' orders for an account and put it into a dynamic excel worksheet that will prompt the end user to select the start and end dates they want to filter "new" orders by. All in all a rather convoluted way to pull a list of new customers between particular dates.

  • Without having the table ddl, I'm guessing here, but this looks like it would work:

    SELECT account0.name as 'name',

    ab.createdon as 'orderdate'

    FROM FilteredAccount as account0

    JOIN (SELECT customerid, createdon = max(createdon)

    FROM FilteredSalesOrder

    WHERE totalamount > 0

    GROUP BYcustomerid) as ab

    ON (account0.accountid = ab.customerid )

    ORDER BY account0.name, ab.createdon asc

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • awesome, thanks wayne, that did the trick. I was able to add a few more columns I wanted to show in the report, its prompting for the start and end dates and all the data showing is perfect, thanks a ton!

    Here is my modified query:

    SELECT account0.name as 'name',

    account0.industrycodename as 'industry',

    account0.numberofemployees as '# of employees',

    account0.owneridname as 'ownername',

    ab.createdon as 'orderdate'

    FROM FilteredAccount as account0

    JOIN (SELECT customerid, createdon = min(createdon)

    FROM FilteredSalesOrder

    WHERE totalamount > 0

    GROUP BY customerid) as ab

    ON (account0.accountid = ab.customerid )

    WHERE (CAST(CONVERT(VARCHAR,ab.createdon,101) as DATETIME) BETWEEN ? AND ?)

    ORDER BY ab.createdon asc

    Only thing I can't seem to figure out now is how to add an additional column from the filteredsalesorder view. Specifically trying to get the total amount from that order. The query below succeeds, but it brings back the oldest order within the user specified dates for all accounts, not just the accounts whos min(createdon) was within that range. I'm sure I'm overlooking something pretty simple

    SELECT account0.name as 'name',

    account0.industrycodename as 'industry',

    account0.numberofemployees as '# of employees',

    account0.owneridname as 'ownername',

    ab.createdon as 'orderdate',

    ab.totalamount as 'total'

    FROM FilteredAccount as account0

    JOIN (SELECT customerid, createdon = min(createdon), totalamount

    FROM FilteredSalesOrder

    WHERE totalamount > 0

    GROUP BY customerid, totalamount) as ab

    ON (account0.accountid = ab.customerid )

    WHERE (CAST(CONVERT(VARCHAR,ab.createdon,101) as DATETIME) BETWEEN ? AND ?)

    ORDER BY ab.createdon asc

  • Johnnymac (9/6/2010)


    awesome, thanks wayne, that did the trick. I was able to add a few more columns I wanted to show in the report, its prompting for the start and end dates and all the data showing is perfect, thanks a ton!

    Here is my modified query:

    SELECT account0.name as 'name',

    account0.industrycodename as 'industry',

    account0.numberofemployees as '# of employees',

    account0.owneridname as 'ownername',

    ab.createdon as 'orderdate'

    FROM FilteredAccount as account0

    JOIN (SELECT customerid, createdon = min(createdon)

    FROM FilteredSalesOrder

    WHERE totalamount > 0

    GROUP BY customerid) as ab

    ON (account0.accountid = ab.customerid )

    WHERE (CAST(CONVERT(VARCHAR,ab.createdon,101) as DATETIME) BETWEEN ? AND ?)

    ORDER BY ab.createdon asc

    Only thing I can't seem to figure out now is how to add an additional column from the filteredsalesorder view. Specifically trying to get the total amount from that order. The query below succeeds, but it brings back the oldest order within the user specified dates for all accounts, not just the accounts whos min(createdon) was within that range. I'm sure I'm overlooking something pretty simple

    SELECT account0.name as 'name',

    account0.industrycodename as 'industry',

    account0.numberofemployees as '# of employees',

    account0.owneridname as 'ownername',

    ab.createdon as 'orderdate',

    ab.totalamount as 'total'

    FROM FilteredAccount as account0

    JOIN (SELECT customerid, createdon = min(createdon), totalamount

    FROM FilteredSalesOrder

    WHERE totalamount > 0

    GROUP BY customerid, totalamount) as ab

    ON (account0.accountid = ab.customerid )

    WHERE (CAST(CONVERT(VARCHAR,ab.createdon,101) as DATETIME) BETWEEN ? AND ?)

    ORDER BY ab.createdon asc

    What I'm trying to do is only show the oldest order for each customer

    Okay, I see where I did the most recent createdon date instead of the oldest.

    Only thing I can't seem to figure out now is how to add an additional column from the filteredsalesorder view.

    However, your change to your query is now getting the oldest createdon date for each customer/totalamount. This is substantially different. If you now want other information about that row, you will have to join back to it to get it.

    SELECT account0.name as 'name',

    account0.industrycodename as 'industry',

    account0.numberofemployees as '# of employees',

    account0.owneridname as 'ownername',

    ab.createdon as 'orderdate',

    fso.totalamount as 'total'

    FROM FilteredAccount as account0

    JOIN (SELECT customerid, createdon = min(createdon)

    FROM FilteredSalesOrder

    WHERE totalamount > 0

    GROUP BY customerid) as ab

    ON (account0.accountid = ab.customerid )

    JOIN FilterSalesOrder fso

    ON fso.customerid = ab.customerid

    AND fso.createdon = ab.createdon

    WHERE (CAST(CONVERT(VARCHAR,ab.createdon,101) as DATETIME) BETWEEN ? AND ?)

    ORDER BY ab.createdon asc

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Thanks Wayne, I learned quite a bit with your help on this.

    -John

  • Johnnymac (9/6/2010)


    Thanks Wayne, I learned quite a bit with your help on this.

    -John

    Glad I could help. Just please respond back and let us all know how things ended up for you!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • All the reports turned out great, above expectations for certain. Was able to use the query in a number of pivot table reports/charts that ended up looking pretty nice. Best of all got a much better understanding of how to approach working with the data as it structured in our CRM system.

    If there are any good SQL books you recommend I'd love the suggestions.

    -John

Viewing 9 posts - 1 through 9 (of 9 total)

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