 క్షిలో మేవిల్ట్ర క్విస్ట్ప్వింర్ర్ మేగిసాడేనినొ కార్ర. ఉనీలో కడిపినొఀటె. కనూర్ర కార్నా పెర్చ కరంపర్. today i wanna discuss one more question which is related to intervals so this is status failure report so let's move to another slide where we will discuss about the question so this is our slide and you can see we have an input table and it contains two columns first is transaction id and second is status and we can see status value is p and f when it comes feed then pass otherwise fail so we want to get those transaction id which comes after the status pass so here we can see we have transaction id 1 status value is p and then transaction id is 2 value is f so f come after the status pass so we want to get in our output as you can see we have transaction id 10 and value is fail our previous transaction before the 10 is 5 and the status value is pass so we want to get in our output so such type of values we want to get in in our output which comes after the status pass so how we can achieve this output by using sql server query so let's move to sql server management studio where we will write the query to get the output as i am using sql 2014 you may have another version of sql server but the query would be same for all the versions doesn't matter what version you are using and i have already created the table and inserted the record on the same so here we can see we have transaction id 1 status is pass and another transaction id is 2 and a status is fail so 2 would become in our output so how we can achieve this output so first of all we need to understand the logic how we can achieve this as you can see in our input table we have transaction id but transaction id is not in sequence as you can see transaction id is 5 then there is some missing transaction id is if we have transaction id in a sequence then we can apply a self join and we can get the output so first of all we need to create a sequence number for creating sequence number we have to use row number so this is our row number and we have to apply over clause and then we can use order by on the behalf of this transaction id so this is transaction id and now we can execute and we can see we have a column and it has the sequence number 1 2 3 4 5 like this and we can give some alias name is sequence so now let's assume you have this sequence number as a transaction id so copy this and go to excel to understand the logic how we can get the output so this is our excel i want to put self join between these two tables so i have pasted two times so here we can see here we can delete this row and this transaction id as well so and now this is our transaction id when a status value is p then we will get in our single table like 1 4 5 and then we want to apply a join between this and this table and in this table we want to get only those status which is pass so here we can see we have 1 4 5 and then 8 so now i want to apply join between these two on the behalf of sequence number so this sequence number value is 1 is equals to and here we can see the sequence number value is 2 so we have to compare between our first row from this pass status table and the second row from this another table so we have to apply this sequence number from left table plus 1 so sequence left plus 1 is equals to sequence right table so when we apply such type of join then we can see we will get the output so now let's move to sequence row management issue you to apply join so now i want to put this query in a city so we can call the city as a city main and as keyword so this is the syntax for the city and now i want to use one more city to get our left table as i have displayed here and in this left table we want to get only those transaction id which status is pass so we can put simply comma and we can use simply nasty city and give the city name so city pass as we are going to get only those transaction id which status is pass so select stick from city main table city main and as keyword missing where status value is pass and then we can see select a stick from city pass when we execute this query we will get those transaction id which status is pass so now we have to apply join between city pass and city main so this is our city main and we want to put alias as a then inner join with city pass as b on a dot sequence number is equals to b dot sequence number as we have explained here our left table is those table which status is pass so we have to add one in our sequence so this is our b table so we have to add plus one and we want to get only values from main table and we have to apply a where clause because we want to get those transaction id which status is fail so a dot status value should be fail and now i want to execute this query and we can see we are going to get the output as we want so i want to select the table so it's very easy to compare between them execute and i am going to maximize so here we can see little bit big yeah so here we can see we have two ten and two zero two so two value is fail so here we can see our first value is pass that is okay then ten so our previous value is five and status value is pass then two zero two so our previous value is pass so by using this query we can get the output so i hope you have understand how we can get the output if you like this video please subscribe our channel for many more videos and press bell icon to get all the notifications for new videos if you need this script it will be available on my facebook page you can couple from there thank you so much for watching