question

SQL-4608 avatar image
0 Votes"
SQL-4608 asked ·

Need help with Update Query

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


sql-server-transact-sql
expectedoutput.png (18.2 KiB)
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MattiasAsplund-3483 avatar image
0 Votes"
MattiasAsplund-3483 answered ·
  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
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.