Update existing Flag based on the date

Transact-SQL
1

I have a table with 4 columns. ID is a unique number , you can have the same id_number with different update_dates. In the current table all FLAGS are set to 'Y', however what i want to do is set the FLAG to 'Y' only for the max update_date for each ID/ else N. any unique ID that has a count > 1 i should look to see the max update_date and flag that with a 'Y' the else 'N' for (historical purposes).

I need an update statement that will update the FLAG in my existing tables based off the update_date.

current setup

ID,ID_NUMBER,UPDATE_DATE,FLAG
1,ws5412,02-25-18,Y
1,ws5412,06-21-18,Y
1,ws5412,01-2-14,Y
1,ws5412,05-8,16,Y

EXPECTED OUTPUT
06-21-18 is flagged 'Y' since its the most recent update_date

ID,ID_NUMBER,UPDATE_DATE,FLAG
1,ws5412,02-25-18,N
1,ws5412,06-21-18,Y
1,ws5412,01-2-14,N
1,ws5412,05-8,16,N

2 
;WITH cte_by_date_order AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY UPDATE_DATE DESC) AS row_num
    FROM dbo.table_name
)
UPDATE cte_by_date_order
SET FLAG = CASE WHEN row_num = 1 THEN 'Y' ELSE 'N' END
3

what if there are 2 rows on same date?

4

Hi yosiasz/Dstyles,

If you would like to update the FLAG column to 'Y' for all of the records with maximum UPDATE_DATE value using ScottPletcher's script, within the ID group, you can replace the ROW_NUMBER() to DENSE_RANK()., like mentioned here under the link:
https://docs.microsoft.com/en-us/sql/t-sql/functions/ranking-functions-transact-sql?view=sql-server-2017

Regards,
gigawatt38
MCSA: SQL 2016 Database Development
MCSE: Data Management and Analytics

5

what if i there are two entries with the same date, (the below table keeps a history of everything) but i only want to highlight the current record with 'Y'. partition works perfect however if same date twice in the table it my choose the wrong line item. this is where i have a lookup table with only current entries no history

ID,ID_NUMBER,status,UPDATE_DATE,FLAG
1,ws5412,pending,06-21-18,Y
1,ws5412,complete,06-21-18,Y
1,ws5412,closed,01-2-14,y
1,ws5412,closed,05-8,16,y

LOOKUP TABLE WITH ONLY current records
ID,ID_NUMBER,STATUS,UPDATE_DATE,FLAG
1,ws5412,PENDING,06-21-18,Y

END RESULT

ID,ID_NUMBER,status,UPDATE_DATE,FLAG
1,ws5412,pending,06-21-18,Y
1,ws5412,complete,06-21-18,N
1,ws5412,closed,01-2-14,N
1,ws5412,closed,05-8,16,N

6

How do you determine that the row with pending status is the 'current' row? Is there a defined status order where the status changes from complete to pending to closed? Or can it go from pending to complete to pending to closed?

7

To take care of dup dates, add another level(s) to the ORDER BY clause to get the specific row you want to be marked the current one. For example, if you want a status of "complete" to be first, then adjust as below. Make other enhancements to the ORDER BY as you need to.

;WITH cte_by_date_order AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY UPDATE_DATE DESC,
        CASE WHEN status = 'complete' THEN 1 ELSE 2 END) AS row_num
    FROM dbo.table_name
)
UPDATE cte_by_date_order
SET FLAG = CASE WHEN row_num = 1 THEN 'Y' ELSE 'N' END
8

Hi

I have tried it and came up with the following SQL
Please let me know if this is correct

Create Data Script 
IF Object_id('tempdb..#TestData', 'U') IS NOT NULL 
  BEGIN 
      DROP TABLE #testdata; 
  END; 

go 

CREATE TABLE #TestData
(ID int 
,ID_NUMBER varchar(100)
,UPDATE_DATE date
,FLAG varchar(1)
)

insert into #TestData select 1,'ws5412',cast('02-25-18' as DATE),'Y'
go 
insert into #TestData select 1,'ws5412',cast('06-21-18' as DATE),'Y'
go 
insert into #TestData select 1,'ws5412',cast('01-2-14' as DATE),'Y'
go 
insert into #TestData select 1,'ws5412',cast('05-8-16' as DATE),'Y'
go 
--
insert into #TestData select 2,'xxyyzz',cast('03-14-16' as DATE),'Y'
go 
insert into #TestData select 2,'xxyyzz',cast('06-21-17' as DATE),'Y'
go 
insert into #TestData select 2,'xxyyzz',cast('05-3-16' as DATE),'Y'
go 
insert into #TestData select 2,'xxyyzz',cast('05-18-18' as DATE),'Y'
go
SQL 
SELECT id, 
       id_number, 
       update_date, 
       CASE 
         WHEN Row_number() 
                OVER( 
                  partition BY id 
                  ORDER BY update_date DESC ) = 1 THEN 'Y' 
         ELSE 'N' 
       END AS flag 
FROM   #testdata