Need help with Update Query

SQL 321 Reputation points
2021-03-05T23:47:46.92+00:00

Hi Folks:

Need help with Update query -

We have data which gets inserted on daily basis as seen in UDate. I would like is check UID with vs Previous day and mark the Previous days rows Notes column with comment (Timely + today's date) that no longer appear.

DECLARE @tblTest TABLE  
(UID int,  
 UDate date,  
 Notes varchar(10))  
  
 INSERT INTO @tblTest values (20, '03/01/2021', NULL)  
 INSERT INTO @tblTest values (30, '03/01/2021', NULL)  
  
 INSERT INTO @tblTest values (20, '03/02/2021', NULL)  
 INSERT INTO @tblTest values (40, '03/02/2021', NULL)  
 INSERT INTO @tblTest values (50, '03/02/2021', NULL)  
  
 INSERT INTO @tblTest values (50, '03/03/2021', NULL)  
 INSERT INTO @tblTest values (60, '03/03/2021', NULL)  
  
SELECT * FROM @tblTest  

EXPECTED OUTOUT

74993-expectedoutput.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
0 comments No comments
{count} votes

Accepted answer
  1. Mattias Asplund 236 Reputation points
    2021-03-06T06:35:52.91+00:00
     DECLARE @tblTest TABLE
     (UID int,
      UDate date,
      Notes varchar(10))
    
      INSERT INTO @tblTest values (20, '03/01/2021', NULL);
      INSERT INTO @tblTest values (30, '03/01/2021', NULL);
    
      INSERT INTO @tblTest values (20, '03/02/2021', NULL);
      INSERT INTO @tblTest values (40, '03/02/2021', NULL);
      INSERT INTO @tblTest values (50, '03/02/2021', NULL);
    
      INSERT INTO @tblTest values (50, '03/03/2021', NULL);
      INSERT INTO @tblTest values (60, '03/03/2021', NULL);
    
    WITH A (UDate) AS
    (
        SELECT DISTINCT UDate FROM @tblTest
    )
    
    SELECT  UID,
            UDate,
            (SELECT 'Timely ' + CONVERT(NVARCHAR(20), 
                                    (
                                        SELECT MIN(UDate) FROM A WHERE UDate > (SELECT MAX(UDate) FROM @tblTest T2 WHERE T2.UID = T.UID) 
                                    )
                                ) AS 'Notes')
    FROM @tblTest T
    
    0 comments No comments

0 additional answers

Sort by: Most helpful