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
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
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)
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
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
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 !