Using MAX() to get the last date from multiple tables

Today I needed to figure out how to get the last date from any of 3 tables.  It is for a stored procedure that is used in Crystal Report to total up sales for brokers for each month in a given year.  We wanted to remove any broker that didn't have any activity that year.  So I wanted to figure out what was the last date a given broker had activity in 3 different tables.  We keep both entry date and update date in the tables, but we decided to use the entry date rather than the update date for this particular report since it was about when they had been active as brokers not when they had updated the tables.  Doing one was simple:

SELECT MAX(TradeDate) AS MaxDate FROM Office.Transactions WHERE BrokerID = 1 AND TradeDate IS NOT NULL

 

Not much magic to it.  Getting the rest wasn't too difficult either:

SELECT MAX(TradeDate) AS MaxDate FROM Office.Transactions WHERE BrokerID = 1 AND TradeDate IS NOT NULL
UNION
SELECT MAX(EntryDate) AS MaxDate FROM Office.IOI WHERE BrokerID = 1
UNION
SELECT MAX(EntryDate) AS MaxDate FROM Office.TradeNotes WHERE BrokerID = 1

 

But that only got me half way as the MaxDate could be different for all 3 tables so I had to find the last date out of the results.  After pondering the syntax a little bit I came up with this:

SELECT MAX(MaxDate) FROM (
SELECT MAX(TradeDate) AS MaxDate FROM Office.Transactions WHERE BrokerID = 1 AND TradeDate IS NOT NULL
UNION
SELECT MAX(EntryDate) AS MaxDate FROM Office.IOI WHERE BrokerID = 1
UNION
SELECT MAX(EntryDate) AS MaxDate FROM Office.TradeNotes WHERE BrokerID = 1) AS X

 

Worked perfectly!  Note that this was done in Sybase SQL Anywhere 11, but I don't think there is anything in there that wouldn't work in MS-SQL or other SQL dialects.  This gives me the last date in any of the tables I want to check.  The same method could be used with MIN() to get the first date of activity.

Have more questions? Submit a request

1 Comments

  • 0
    Avatar
    Bharat Prajapati

    thank u so much

Please sign in to leave a comment.
Powered by Zendesk