Need help with Update Query

Question

question

SQL-4608 asked · Mar 05 at 11:47 PM

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

sql-server-transact-sql

expectedoutput.png (18.2 KiB)

10 |1000 characters needed characters left characters exceeded

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

Answer 1 · 2021-03-06T06:35:52.91Z

MattiasAsplund-3483 answered · Mar 06 at 06:35 AM

  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

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

question