Return records for most recent date

[Snakeroot]

I'm basically running a query like this example:

SELECT RT.serial, Rt.Customer, RT.Invoice, RT.RentalTxnDate, RT.TxnLineID,RT.Location, CD.InvoiceTxnDate
FROM RentalsView as RT
INNER JOIN InvoicesView as CD
    ON RT.serial = CD.serial
    AND RT.customer = CD.customer
    AND RT.RentalTxnDate >= CD.InvoiceTxnDate
GROUP BY 
    RentalTxnDate, Customer, Invoice, Serial, TxnLineID, Location, InvoiceTxnDate

I'm ending up with the results I want, but I want to narrow the results down to the most recent date (and not show the records corresponding to the previous dates). For example, I'm returning these fields:

RentalTxnDate, Customer,Invoice,Serial,TxnLineID,Location,InvoiceTxnDate


Data looks something like: 

8-3-2007 | Killian | 19209 | 42885C | D4D3-1188518188 | OC | 1-1-2007 
8-3-2007 | Killian | 19209 | 42066C | D4D4-1188518188 | OC | 1-1-2007
9-3-2007 | Killian | 22013 | 42885C | DF97-1191364685 | OC | 1-1-2007
9-3-2007 | Killian | 22013 | 42066C | DF98-1191364685 | OC | 1-1-2007

So, basically there was an invoice made on 1/1/2007, for serial#'s 42885C and 42066C. On 8/3/2007 there was a rental invoice created for these two serial #'s. Then on 9/3/2007, there was another rental invoice created for these two serial #'s.

In this list of results that has multiple invoices for multiple customers in multiple locations, when there is one original invoice and multiple rental invoices that correspond to the original invoice. How can I show the records that correspond to the most recent rental invoice date? In this case it would be 9-3-2007.

 

[SQLBill]

What data type is your date columns? If they are datetime, then there are ways to do it. However, if they are varchar, then the sort is dictionary and you will have trouble getting what you want. Dictionary sort works like this:

One (1)
Ten (10)
Two (2)

So, with varchar dates, I don't think you could guarantee a correct result unless you first converted the dates to datetime data type. Date manipulation is the main reason dates should ALWAYS be saved as datetime.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Snakeroot]

Thanks Bill... I don't have any control over the fact that the data comes in as varchar, however I can cast it as datetime.

Can you show me the way to do it using datetime data? Thanks!

 

[Snakeroot]

I was left hangin on this one.... anyone else out there have a solution that would work? I'd really appreciate a nudge in the right direction. Thanks!

 

[navigator703]

Have you already tried the max feature. Maybe something like this:

SELECT MAX (RT.RentalTxnDate),RT.serial, Rt.Customer, RT.Invoice, RT.TxnLineID,RT.Location, CD.InvoiceTxnDate
FROM RentalsView as RT
INNER JOIN InvoicesView as CD
    ON RT.serial = CD.serial
    AND RT.customer = CD.customer
    AND RT.RentalTxnDate >= CD.InvoiceTxnDate
GROUP BY 
    RentalTxnDate, Customer, Invoice, Serial, TxnLineID, Location, InvoiceTxnDate

~ every wise man started out by asking many questions~