How to flag based on maxdate value as New and other rows flag as OLD

1

Here is my sample query, where i see only max date, all the other rows, that doesn't have a maxdate, would like to flag as OLD, is it possible.
(SELECT id, max(modified_date) max_modified FROM
(SELECT * FROM base_table
UNION
SELECT * FROM incremental_table)
GROUP BY id) t2

Thank you very my for the helpful info.

2

;WITH CTE AS (SELECT * FROM base_table
UNION SELECT * FROM incremental_table)
SELECT *, CASE WHEN modified_date<MAX(modified_date) OVER (PARTITION BY id)
THEN 'Old' ELSE 'Newest' END AS AgeStatus
FROM CTE;

You can't do SELECT * with GROUP BY id, you'd have to add every column to the GROUP BY clause, which would invalidate the MAX() operation.

1 Like
3

Thank you sir. good logic. thanks.

4

hi

another way .. but mind you depends on situation

SELECT TOP 1
         *
FROM     (
                SELECT *
                FROM   base_table
                UNION
                SELECT *
                FROM   incremental_table )
ORDER BY modified date DESC
5

a wild guess since you provided no sample data

use sqlteam
go

declare @base_table table(id int, modified_date date)
declare @incremental_table table(id int, modified_date date)

insert into @base_table
select distinct  top 100 object_id, create_date object_id 
from sys.objects order by 1 asc

insert into @incremental_table
select distinct  top 100 object_id, create_date object_id 
from sys.objects order by 1 desc


declare @maxdate date

select @maxdate = max(modified_date)
from (
SELECT modified_date  FROM @base_table
	UNION 
	SELECT modified_date  FROM @incremental_table
) a
select @maxdate



select id, *,
      case 
	     when modified_date >= @maxdate then 'New'
		 else 'Old' end as status
from (
	SELECT * FROM @base_table
	UNION 
	SELECT * FROM @incremental_table
) a