CODEWITHSUNDEEP
sqlsql-server
j.strugnell
j.strugnell

Reputation: 407

SQL Server Function for Maximum Date in Different Fields

I have multiple related tables in a database and each can be updated separately and have their own LastUpdated Date field. In one of these tables there is more than one LastUpdated field each indicating from which source that record was updated. We commonly query these multiple tables as a single item with joins and thus I would like to know for each record what the most recent LastUpdated record is across all joins. I know this can be achieved with many sub-queries but I was wondering whether there was something along the lines of the Coalesce function into which you can pass many fields and it returns the first non-null value.

So it would read something like:

SELECT a.a_id, 
       a.name, 
       b.b_id, 
       b.detail, 
       c.c_id, 
       c.otherfield, 
       Maxdate(a.lastupdated, a.lastupdatedfromweb, b.lastupdated, 
       c.lastupdated) AS 
       LastUpdatedDate 
FROM   a 
       INNER JOIN b 
               ON a.a_id = b.a_id 
       INNER JOIN c 
               ON b.b_id = c.b_id

Any ideas? Could this be written as a custom function or does it exist in the box? I am working on SQL Server 2005 and 2008 if that helps.

Upvotes: 1

Views: 83

Answers (2)

GarethD
GarethD

Reputation: 69789

In 2008+ you can use CROSS APPLY to create a row for each of your dates, then select the Max:

SELECT 
 a.a_Id,
 a.Name,
 b.b_Id,
 b.Detail,
 c.c_Id,
 c.OtherField,
 ld.LastUpdatedDate
FROM 
 a INNER JOIN b ON a.a_Id = b.a_Id 
   INNER JOIN c ON b.b_Id = c.b_Id
   CROSS APPLY
   (    SELECT  LastUpdatedDate = MAX(LastUpdatedDate)
        FROM    (VALUES
                    (a.LastUpdated), 
                    (a.LastUpdatedFromWeb), 
                    (b.LastUpdated), 
                    (c.LastUpdated)
                ) d (LastUpdatedDate)
    ) ld

You could also do this as a correlated subquery, but during optimisation SQL Server will rewrite correlated subqueries as OUTER APPLY so I prefer to just cut a step out and write the APPLY myself, as I have found some unusual behaviour when SQL deconstructs the correlated subquery and rewrites it as an APPLY. More details of this are described in this answer

For 2005, I think you will need to use a slightly different method as it doesn't support table valued constructors:

SELECT 
 a.a_Id,
 a.Name,
 b.b_Id,
 b.Detail,
 c.c_Id,
 c.OtherField,
 ld.LastUpdatedDate,
 (  SELECT  MAX(CASE Number 
                    WHEN 1 THEN a.LastUpdated
                    WHEN 2 THEN a.LastUpdatedFromWeb
                    WHEN 3 THEN b.LastUpdated
                    WHEN 4 THEN c.LastUpdated
                END)
    FROM    (SELECT TOP 4 Number = ROW_NUMBER() OVER(ORDER BY object_id) 
            FROM sys.all_objects) n
) AS LastUpdated
FROM 
 a INNER JOIN b ON a.a_Id = b.a_Id 
   INNER JOIN c ON b.b_Id = c.b_Id;

Or:

SELECT 
 a.a_Id,
 a.Name,
 b.b_Id,
 b.Detail,
 c.c_Id,
 c.OtherField,
 ld.LastUpdatedDate,
 (  SELECT  MAX(LastUpdated)
    FROM    (   SELECT a.LastUpdated UNION ALL
                SELECT a.LastUpdatedFromWeb UNION ALL
                SELECT b.LastUpdated UNION ALL
                SELECT c.LastUpdated
            ) d
) AS LastUpdated
FROM 
 a INNER JOIN b ON a.a_Id = b.a_Id 
   INNER JOIN c ON b.b_Id = c.b_Id;

Unfortunately I no longer have any 2005 instances installed so I can't test this.

EDIT

Just realised that you wanted the source of the field too, and also remembered that 2005 does support the use of APPLY, so for 2008+:

SELECT 
 a.a_Id,
 a.Name,
 b.b_Id,
 b.Detail,
 c.c_Id,
 c.OtherField,
 ld.LastUpdatedDate,
 ld.FieldName
FROM 
 a INNER JOIN b ON a.a_Id = b.a_Id 
   INNER JOIN c ON b.b_Id = c.b_Id
   CROSS APPLY
   (    SELECT  TOP 1 LastUpdatedDate, FieldName
        FROM    (VALUES
                    (a.LastUpdated, 'a.LastUpdated'), 
                    (a.LastUpdatedFromWeb, 'a.LastUpdatedFromWeb'), 
                    (b.LastUpdated, 'b.LastUpdated'), 
                    (c.LastUpdated, 'c.LastUpdated')
                ) d (LastUpdatedDate, FieldName)
        ORDER BY LastUpdated DESC
    ) ld

For 2005:

SELECT 
 a.a_Id,
 a.Name,
 b.b_Id,
 b.Detail,
 c.c_Id,
 c.OtherField,
 ld.LastUpdatedDate,
 ld.FieldName
FROM 
 a INNER JOIN b ON a.a_Id = b.a_Id 
   INNER JOIN c ON b.b_Id = c.b_Id
   CROSS APPLY
   (    SELECT  TOP 1 LastUpdatedDate = LastUpdated, FieldName
        FROM    (
                    SELECT a.LastUpdated, FieldName = 'a.LastUpdated' UNION ALL
                    SELECT a.LastUpdatedFromWeb, 'a.LastUpdatedFromWeb' UNION ALL
                    SELECT b.LastUpdated, 'b.LastUpdated' UNION ALL
                    SELECT c.LastUpdated, 'c.LastUpdated'
                ) d
        ORDER BY LastUpdated DESC
    ) ld

Upvotes: 2

Chirag Sutariya
Chirag Sutariya

Reputation: 362

 SELECT 
 a.a_Id,
 a.Name,
 b.b_Id,
 b.Detail,
 c.c_Id,
 c.OtherField,
 MAX(SELECT a.LastUpdated, a.LastUpdatedFromWeb, b.LastUpdated, c.LastUpdated FROM a INNER JOIN b ON a.a_Id = b.a_Id INNER JOIN c ON b.b_Id = c.b_Id) AS LastUpdatedDate
FROM 
 a INNER JOIN b ON a.a_Id = b.a_Id 
   INNER JOIN c ON b.b_Id = c.b_Id

Upvotes: 0

Related Questions