 Hello, welcome to SSHiritech, so see in this side and this is continuation of SQL Server interview questions and answers. So today we are going to discuss one more interview question and here we have input table and it would have only three columns, first is card number, then the value, then date. So as we can see in the card number we are having C1, C1, C1, then C2, C2, C2, C2 and C3. In the value column we are having some values and some missing values. As we can see for C1 we can check for the third row. So in the value we don't have any value. Then for C2 as we can see in the second row, so value is missing. So we just want to get these values which are missing by the previous one. So here as we can see the previous value of C1 is 010 and date is 2019 0701. So this value should be get in 2019 0801. Here the value is missing. This is for C1. Then for C2 as we can see we are having 1000 is the previous value. So this 1000 should be updated in the next row. So as we can see in the output for C1 we are having 100, then 1100 and then the missing value now which is 1100 which is the previous value. And then for C2 as we can see first row is 1000, second was the missing. So here we are having 1000 because the previous value is 1000. So how we can get this output? So let's move to Seacoast over management studio to write the query for getting this output. So as I am using SQL 2017 you may have another version of Seacoast over. So here first of all I would like to check if this table is already exist in the database or not. If that table exist then we just want to drop that table. So we can execute the first statement. Now I just want to create the table. So we can execute this create statement. Then I just want to insert data on that table. So let me execute this insert statement to insert the records on the table. So let me execute this query. So as we can see 8 rows affected. It means 8 rows have been inserted in the table. So now I just want to check the data on the table. So we can write select a stick from our table. Now let me execute this query. So as we can see we are having the same metadata as we seen in the slide. So before going to watch this video you can pause this video at this point. Let you think about how we can get the previous value and that value should be updated in the next row. So don't think very complex logic. Let me clear it is very simple. So here we can use the lag function to get the previous value. So we can use the lag function. Then inside that lag function we can use the value and then we have to use the over clause because this is the syntax for lag function. Then we have to use the partition by and here partition by should be on card number. So this is the card number. Then we have to use the order by. So in case of order by as we can see in date. So date is in order. So we can put the date. Now we can put a alias name with lag value. Let me execute this query to check the output. So as we can see here we are having null value and this is the previous value. So this is the first value that's why here we are having null. Then for the second row previous value is 100. So this 100 is coming in the lag value column. Then next is null. So in case of null it is going to pick the previous value that is double one zero. So double one zero is coming on lag value. So this is the logic we can implement here we just want to get the card number. So we can use a card number then we want to get the value. So in case of value we can use the is null function. So is null and this is the value if value column is null then we want to get the value from the lag function. So this is the null value. So we can put as value. Next is date. So we want date. Now we can execute this query and we can compare the output. So as we can see we are getting double one zero. So previous value is double one zero. Next is 1000. So we are getting the output as we were expected. So this is the very simple logic by which we can get the output. So I hope you have understand how we can replace the missing value by the previous values. Thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos. Thank you so much.