SQL Server - Update Query for history

Rohit 231 Reputation points
2021-11-11T17:43:41.163+00:00

Hi,

I am trying to update a table & am facing difficulties with it, below i have explained the problem statement & the required solution using an example

Example:

Table : Items

CREATE TABLE #Items  
(  
	ItemId varchar(50) NOT NULL,  
	ItemName varchar(50),  
	QuantityAvailable varchar(50),  
	Comments varchar(50),  
	RecCreatedDate datetime NOT NULL  
)  
  
INSERT INTO #Items  
SELECT 'M1001','AB','10','None','2021-09-01 00:00:00.000'					UNION  
SELECT 'M1002','CD','4','Need more of these','2021-09-01 00:00:00.000'		UNION  
SELECT 'M1003','DEF','1','None','2021-09-01 00:00:00.000'					UNION  
SELECT 'M1004','DEF','','','2021-09-01 00:00:00.000'						UNION  
SELECT 'M1001','AB','5','None','2021-09-02 00:00:00.000'					UNION  
SELECT 'M1002','CD','2','Need more of these','2021-09-02 00:00:00.000'		UNION  
SELECT 'M1003','DEF','1','None','2021-09-02 00:00:00.000'					UNION  
SELECT 'M1004','DEF','20','','2021-09-02 00:00:00.000'						UNION  
SELECT 'M1001','AB','5','None','2021-09-03 00:00:00.000'					UNION  
SELECT 'M1002','CD','2','Need more of these','2021-09-03 00:00:00.000'		UNION  
SELECT 'M1003','DEF','1','None','2021-09-03 00:00:00.000'					UNION  
SELECT 'M1004','DEF','20','None','2021-09-03 00:00:00.000'					UNION  
SELECT 'M1001','AB','','','2021-09-04 00:00:00.000'							UNION  
SELECT 'M1002','CD','','','2021-09-04 00:00:00.000'							UNION  
SELECT 'M1003','DEF','','','2021-09-04 00:00:00.000'						UNION  
SELECT 'M1004','DEF','','','2021-09-04 00:00:00.000'						UNION  
SELECT 'M1001','AB','','','2021-09-05 00:00:00.000'							UNION  
SELECT 'M1002','CD','','','2021-09-05 00:00:00.000'							UNION  
SELECT 'M1003','DEF','','','2021-09-05 00:00:00.000'						UNION  
SELECT 'M1004','DEF','','','2021-09-05 00:00:00.000'						UNION    
SELECT 'M1001','AB','10','Required','2021-09-06 00:00:00.000'					UNION  
SELECT 'M1002','CD','4','not required','2021-09-06 00:00:00.000'		UNION  
SELECT 'M1003','DEF','1','None','2021-09-06 00:00:00.000'					UNION  
SELECT 'M1004','DEF','','','2021-09-01 00:00:00.000'						UNION  
SELECT 'M1001','AB','','','2021-09-07 00:00:00.000'							UNION  
SELECT 'M1002','CD','','','2021-09-07 00:00:00.000'							UNION  
SELECT 'M1003','DEF','','','2021-09-07 00:00:00.000'						UNION  
SELECT 'M1004','DEF','','','2021-09-07 00:00:00.000'   

Below is the select query result for the same

148613-image.png

As seen from the image the data in the column QuantityAvalibale &Comments for the dates 4,5 & 7th is blank , this is due to an issue in other tables , causing blank values here

This needs to be corrected , the blank values needs to be replaced / updated with the most recent value

More details in the below scenarios.

Considering Id : M1001 for scenario1

148595-image.png

here the blank values for the dates 4,5 & 7th needs to be replaced with their most recent values

So for the dates 4 & 5 , the data that needs to be populated is that of the 3rd day
And for the 7th day, it should have the value as of the 6th day of the month

So after the data updates, the values in the columns should be like below
(The same applies for id : M1002 & M003)

148614-image.png

Considering id M1004 for scenario2:

here in addition to scenario1 , the values in the initial dates of the month do not have any values for columns QuantityAvailable & Comments , so a few days later the value is received

148557-image.png

So while updating the data only the values for dates after which we receive the first values should be updated , in this case after 3rd day of the month

148641-image.png

Also the table has more than 100000 records.

Facing some issues forming the update query here , would really appreciate the help here.

Thanks in advance !

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,432 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,689 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Cooper 8,481 Reputation points
    2021-11-11T19:20:09.637+00:00
    ;With cte As
    (Select i.ItemId, i.RecCreatedDate, i.Comments,
      IsNull(Cast(SubString(
        Max(Cast(Convert(Char(23), i.RecCreatedDate, 112) As binary(23)) + Cast(NullIf(i.Comments, '') As binary(50)))
       Over(Partition By i.ItemId, Month(i.RecCreatedDate) Order By i.RecCreatedDate
         Rows Unbounded Preceding), 24, 50) As varchar(50)), '') As NewComments
    From #Items i)
    Update cte Set Comments = NewComments
    Where Comments = '';
    
    --  Check Result
    Select *
    From #Items
    Order By ItemId, RecCreatedDate;
    

    Tom

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. EchoLiu-MSFT 14,601 Reputation points
    2021-11-12T08:01:38.36+00:00

    Please also check:

     ;WITH cte  
     as(SELECT i1.ItemId,i1.ItemName,  
     CASE WHEN i1.QuantityAvailable='' THEN i2.QuantityAvailable  
     ELSE i1.QuantityAvailable END QuantityAvailable,i1.RecCreatedDate,  
     CASE WHEN i1.Comments='' THEN i2.Comments  
     ELSE i1.Comments END Comments,  
     i2.RecCreatedDate RecCreatedDate2  
     FROM #Items i1  
     LEFT JOIN #Items i2 ON i1.ItemId=i2.ItemId   
     AND DATEDIFF(d,i2.RecCreatedDate,i1.RecCreatedDate)=1)  
     ,cte2 as(SELECT c.ItemId,c.ItemName,CASE WHEN c.QuantityAvailable='' THEN i3.QuantityAvailable  
     ELSE c.QuantityAvailable END QuantityAvailable,  
     CASE WHEN c.Comments='' THEN i3.Comments  
     ELSE c.Comments END Comments,c.RecCreatedDate  
     FROM cte c  
     LEFT JOIN #Items i3 ON c.ItemId=i3.ItemId   
     AND DATEDIFF(d,i3.RecCreatedDate,c.RecCreatedDate)=2)  
      
     UPDATE #Items  
     SET QuantityAvailable=c.QuantityAvailable  
     ,Comments=c.Comments  
     FROM cte2 c  
     WHERE #Items.ItemId=c.ItemId AND  #Items.RecCreatedDate=c.RecCreatedDate;  
      
      
     SELECT * FROM #Items  
     ORDER BY ItemId,RecCreatedDate   
    

    Output:
    148812-image.png

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Regards,
    Echo


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.