Selecting maximum date from multiple tables

[MdotButler]

I need to select a last activity date for a customer based on various dates stored in child records. For instance the customer has a setup_date, an order with an order_date, an invoice with an invoice_date and a payment with a payment_date. I need to present the newest date of all the dates.

select customer.*, 
ISNULL((select max(order_date) from orders where order.custid=customer.custid),customer.setup_date) as max_order_date,
ISNULL((select max(invoice_date) from invoice where invoice.custid=customer.custid),customer.setup_date) as max_invoice_date,
ISNULL((select max(payment_date) from payment where payment.custid=customer.custid),customer.setup_date) as max_paymentr_date
from customer

I have gotten as far as the above but how do I get the greatest of the selected dates?

TIA
Mark

 

[bborissov]

SELECT
   ISNULL(Ord.order_date), customer.setup_date) AS max_order_date,
   ISNULL(Inv.invoice_date), customer.setup_date) AS max_invoice_date,
   ISNULL(Pmt.payment_date), customer.setup_date) AS max_payment_date
from customer
LEFT JOIN (SELECT custid, max(order_date)
                  from orders
                  GROUP BY custid) Ord
ON Ord.custid = Customer.custid
LEFT JOIN (SELECT custid, max(invoice_date)
                  from invoice 
                  GROUP BY custid) Inv
ON Inv.custid = Customer.custid
LEFT JOIN (SELECT custid, max(payment_date)
                  from payment
                  GROUP BY custid) Pmt
ON Pmt.custid = Customer.custid

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLBill]

Boris,

I think Mark wants (and he confirm or correct me) this:

Current code returns:

Customer Order Invoice Paymentr
Bill 2007-05-01 2007-05-02 NULL
Boris 2007-05-05 2007-05-10 2007-05-15
Mark 2007-05-10 NULL NULL

He then wants the full code to return:

Customer MaxofDates
Bill 2007-05-02
Boris 2007-05-15
Mark 2007-05-10

Mark - correct me if I'm wrong or if I don't have it complete.

-SQLBill


Posting advice: FAQ481-4875

 

[MdotButler]

SQLBill you are correct. I only want one date per customer showing the most recent activity date.

 

[MdotButler]

In Visual FoxPro I would issue, MAX('2007/05/02', '2007/05/01', '2007/05/12') and the command would return the maximum date in the string of dates, in this case the '2007/05/12' date. This seemed to be something that would have been done before, or is the solution so simple I can't see it.

 

[AlexCuse]

Perhaps a user-defined function is in order?

Something like this could work:

[COLOR=blue]create[/color] [COLOR=#FF00FF]function[/color] actDate (@date1 [COLOR=#FF00FF]datetime[/color], @date2 [COLOR=#FF00FF]datetime[/color], @date3 [COLOR=#FF00FF]datetime[/color])
returns [COLOR=#FF00FF]datetime[/color]
[COLOR=blue]as[/color]

[COLOR=blue]begin[/color]

[COLOR=blue]declare[/color] @m [COLOR=#FF00FF]datetime[/color]

[COLOR=blue]declare[/color] @t [COLOR=blue]table[/color] (mDate [COLOR=#FF00FF]datetime[/color])
[COLOR=blue]insert[/color] @t
[COLOR=blue]select[/color] @date1
union all [COLOR=blue]select[/color] @date2
union all [COLOR=blue]select[/color] @date3

[COLOR=blue]select[/color] @m = [COLOR=#FF00FF]max[/color](mDate) [COLOR=blue]from[/color] @t

[COLOR=blue]return[/color] @m

[COLOR=blue]end[/color]

But there are other ways to do it I am sure.

You can test this function like so:

[COLOR=blue]select[/color] dbo.actDate ([COLOR=#FF00FF]getdate[/color](), [COLOR=#FF00FF]getdate[/color]() + 1, [COLOR=#FF00FF]getdate[/color]() - 1)

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[r937]

too bad sql server doesn't support the standard sql GREATEST function

r937.com | rudy.ca

 

[sunila7]

Can we assume max_paymentr_date > max_invoice_date > max_order_date > C.setup_date ?

Then this might work?

SELECT C.*, COALESCE(max_paymentr_date,max_invoice_date, max_order_date, C.setup_date ) 
FROM
(select customer.*, 
(select max(order_date) from orders where orders.custid=customer.custid) as max_order_date,
(select max(invoice_date) from invoice where invoice.custid=customer.custid) as max_invoice_date,
(select max(payment_date) from payment where payment.custid=customer.custid) as max_paymentr_date
from customer) C

Sunil

 

[MdotButler]

I do not think COALESCE() will work as it returns the first nonnull expression among its arguments. In my situation there may be dates returned from all the queries and any one of them could be the most recent.

For example a customer may have been setup 2007/01/01, placed an order 2007/01/15, invoiced on 2007/01/17 and paid on 2007/02/15. They may have also placed a new order on 2007/04/01 which is the date which I am looking to have returned.

If I understand the COALESCE() documentation, whichever date was first in the arguments would have been returned regardless of the value because all the dates would have been nonnull.

 

[unclejimbob]

What about something like this...

select customer.*,ISNULL(max(a.the_date),setup_date) from
customer
left join (
select custid, max(order_date) as the_date from orders
group by custid
union
select custid, max(invoice_date) as the_date from invoice
group by custid
union
select custid, max(payment_date) as the_date from payment
group by custid
) a
on a.custid=customer.custid
group by
customer.custid,
customer.setup_date
<all otrher customer columns>

Its ugly but I'm pretty sure it will work. Feel free to comment/improve/shoot it down.