 Hello friends, welcome to SSUlitech. So, see this site and this is continuation of SQL Server interview questions and answers. So, today we gonna discuss a very important interview question that is related to dead time calculations. So, let's start with our question. So, here we have total three questions. This is our first question. As we can see, we have an input table that contains four columns. First is ID, name, date of birth and date of joining. And in our output, we want all the columns but we want to get those employees, those contain the date of joining and day of date of birth are same. For ID3, date of joining is 2015, 08 and 20 and date of birth 1991, 06 and 20. So, 20 is same for date of birth and date of joining. So, we want to get in our output as same in case of ID4 and 5. So, we want to get these employees in our output. So, go to SQL Server Management Studio to write the query for getting this output. So, this is our query to create the table. So, I gonna create the table. So, we can execute our query statement. So, as we can see, commands completed successfully. So, table has been created successfully. Now, I gonna insert record on it. So, we can execute our insert statement. So, five rows affected. So, data have been inserted successfully in our table. Now, we can check the data. So, select star from our table. So, we can execute our selected statement. So, as we can see, we are getting the data. Question is, we want both employees which day of date of birth and day of date of joining are same. We can calculate day for date of birth should be equals to day of date of joining. Now, we can execute our selected statement. So, as we can see, we are getting three employees for ID3, 4 and 5. Go to our slide. As we can check, we are getting the same metadata as we are getting in our SQL Server. So, this is our question one. Let's move to question two. So, here we want to get those employees which day of date of joining and day of date of birth are same and month of date of joining and month of date of birth are same. For ID4, as we can see in our date of birth, we have April and in our date of joining, we have April. So, months are same and once we move to our date, then we can see 10 in our date of birth and 10 in our date of joining. So, conditions are going to match. So, this record will be moved to our output. Let's move to ID5. As we can see, month of date of birth is told and month of date of joining is told. So, our first condition is going to be satisfied. Now, move to our second condition, day of date of birth and day of date of joining. So, this condition is also going to be satisfied. So, this record will move to our output. So, go to our SQL Server Management Studio to write the query. So, here we need to write select star from our table and this time we want to get days of date of birth should be equals to day of date of joining. This is our condition 1 and in our condition 2, month of date of birth should be equals to month of date of joining. Now, we can execute our select statement which we have created right now. So, as we can see, we are getting ID4 and 5. In our slide, as we can see, we are getting ID4 and 5. So, this is query by which we can get our output. Let's move to our last question. So, in this question, we want to get minimum age employees detail which has completed 3 years in the organization. As we can see, let's assume our current date is 2018, 04 and 21. So, this is our current date. Once we compare between current date to our date of joining, then we will get ID3, 4 and 5. Once we compare between all these 3 IDs, then we can find ID3 has the minimum date of birth. So, this will move to our output. So, how we can get this output by writing the query. So, go to our SQL Server Management Studio. Here, we need to write the query. So, I want to use select a stick from our table. So, here we want those employees which have been completed 3 years in the organization. So, we need to use depth formula and here we need to specify the interval. So, our interval in years. So, we can specify y and y. Then we need to specify starting date. That is our date of joining, then end date. So, as we can specify 2018, 04 and 21. And here we need to specify if this value is greater or equals to 3. So, we want those employees detail. So, we are getting 3 employees with ID3, 4 and 5. So, these 3 employees have been completed 3 years in the organization. And here we want to generate custom by using row number for the minimum age employee. So, we need to specify over. Then we need to specify order by and date of birth in decreasing order. And we can specify an alias name with rw. Now, we can execute our selected statement. So, as we can see, we are getting a row number with 1, 2 and 3. 1 has the minimum age. 2 is the second highest and 3 is the maximum. So, we want to get only those employees which rw is 1. So, we can use CT with CT temp. So, this is a syntax for CT. And here we want to select only those employees where rw value are equals to 1. Now, we can execute our selected statement. As we can see, we are getting ID3. So, this is our minimum age employee which has been completed 3 years in the organization. So, 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.