 Hello friends, welcome to SSUnitech. My name is Suseel Singh and this is continuation of SQL Server interview questions and answers. So today I am going to discuss how we can calculate the current value, previous value and next value without using any predefined function which is all level in SQL Server by which we can directly calculate the values for next and previous. Let's move to another slide where we will discuss what I want to say. So this is our slide and you can see we have an input table and it contains only two columns, first is customer ID and second is customer name. By using this table we want our output and output we want four columns, first is customer ID and second is customer name which is available in our input table. And we want to calculate two additional columns, first is for previous name and second is for next name. In previous name we can see for customer monies we don't have any previous value for this customer. So we have to display and a value is not available. And let's move to Deepak. In case of Deepak we can see previous customer is available which is monies. So we want to display monies in our previous name column. For monies we have Deepak is our next value. So we have to display the Deepak in next name column. So this is our input and this is our output table. So by using this input we want to calculate this output. Before going to SQL Server Management Studio let's move to Excel where we will discuss the logic how we can calculate the previous and next name values. So this is our Excel and you can see this is our input table and we want to this output. So we have to apply self join in this input table. So let's move to copy and I want to paste here. And again I want to paste here. And we want to apply left join by which we want to all the columns which is available in our input table we want to display in our output. So we have to apply left join between these two tables. So this is our table one and this is our table two. Here we need to apply the join on the behalf of ID. So let's increase ID by one. So we want to add one on this table two ID. So here I want to add one and then we can scroll. So values will be automatically generated. So here we can see now I want to apply a join between these two tables. We want to calculate the all the values which is available in our left table. And we want to calculate the values when this ID which is newly generated ID which is an ID we can say this column is newly generated ID. So this is our NID. We want to apply a join on the behalf of T1.ID and T2.NID. In the case of monies we don't have any values. So it will return null for the ID one. When we move to our second customer which is Deepak. So in case of Deepak Deepak's ID is two. So we can see NID two is available over there. So it will pick the customer name. So customer name is monies. So monies will be displayed here. So this is monies and let's move to another customer which is Vipul. So Vipul's customer ID is three. So three is also available in NID and customer name is Deepak. So Deepak will be displayed. So this is our Deepak. So as so on we can face the values from there. So this column is our previous value. So finally we have calculated the value for previous name. Now move to next name. So here we can see we have calculated the value for NID and we have added one on the ID. So when we are going to calculate the value for next name we have to subtract by one. So one minus one. This value should be zero and we can scroll and we can see value has been calculated automatically. We need to add one more column where we will calculate the value for next name. So this column should be reflect for next name. When we are going to put left join between T1 and T2 on the behalf of ID. So for the monies ID is one and when we move to NID in T2 table then we can see it reflect for Deepak. So next name value should be Deepak will be reflect over here. So this value would be Deepak. And when we move to our second customer which is Deepak then we can see its ID is two. When we move to T2 table then we can see ID2 is also available over there and the value is Vipul. So Vipul will be copied. So as we can see we have calculated the value for the same and we can use the same logic by which we can get the previous and next value. So now let's move to SQL Server Management Studio where we will write the query by which we can calculate the output. So this is our SQL Server and I am using 204 version of SQL Server. This is our script to create any table. Table has been created successfully and I am going to insert the record as I have displayed in our slide. Six rows affected so value has been inserted successfully in our table. We can select and we can get the input table as we have displayed in our slide. So this is our slide. We have six rows and we have the same metadata as I have displayed in slide. As I have displayed we have to put left join in same table then we have to write left join and then we have to use this table and I am going to put alias name which is for first I am going to use for previous and this is our current and on the behalf of id. So this is our current dot id should be equals to previous table dot id and when we are going to calculate the previous value then we have to add one in our previous id as I have displayed in excel. So here we can see when we are going to calculate for an id when we are dealing with previous name then an id has been calculated when we add on this id by one. So here we have added and now I am going to execute this query. So here we can see we have get the data. So we want to get all the information which is available in our left table which is current value table and we need to calculate the value for right table only for name. So previous dot name and now I am going to execute this query. So there is one null record and then we can see this is our previous value and Deepak is our previous value we pull it previous so all the data is reflected as we want. So we have to use is null for replace this null value and we have to specify a name and this is our previous name or we can say this is our pname. So previous name has been calculated successfully. Now I am going to put one more left join on this result set with the same table and it will calculate the value for next. This is our alias name and we are going to apply the condition current dot id is equals to next dot id should be minus one as I have displayed as I have explained in excel. When we subtract minus one in our id then we can calculate the n id so it has been successfully reflected and now I am going to copy this code and paste it here and now I don't want to get the name from previous. This time I want to get the name from next and this is our next. Now I am going to execute this query. Once we execute this query our output is same as we want in our slide. So this is our slide. So this metadata and this metadata both are same. So I hope you have understood how we can calculate the values by using SQL Server query. So thank you so much for watching this video. If you like this video please subscribe our channel for many more videos.