How do I get the next record based on a condition using a SQL Query?

Question

I have a car hire table which records all the dates a car is on hire Onhire and when it's returned Offhire. I've been asked to provide the next or follow on hire company name in the results table but I'm not sure how to do it. The hire table is structured as follows:

---------------
| Hire        |
---------------
| Id          |
| CarId       |
| Onhire      |
| Offhire     |
| HireCompany |
|-------------|

If I run a basic select against that table I see the following data. I've added a WHERE to pull back a specific car that is still on hire and has a follow on hire shortly after (I am using UK date formatting).

Id  |  CarId |  Onhire     |  Offhire    |  HireCompany
-------------------------------------------------------
10  |  272   |  2019-01-01 |  2019-03-01 |  Company A
11  |  272   |  2019-03-02 |  2019-04-01 |  Company B
-------------------------------------------------------

As you can see, the car is currently on hire until 01/03/2019 but after that, it is going on hire to Company B on the 02/03/2019. I need my query to show that the car is on hire at the moment but in a column called ForwardHire (or whatever) show the NEXT company that has it on hire as well as a column that shows the next hire start date.

So, my query would produce the following desired result:

Id  |  CarId |  Onhire     |  Offhire    |  ForwardHire |  ForwardHireDate
---------------------------------------------------------------------------
10  |  272   |  2019-01-01 |  2019-03-01 |  Company B   |  2019-03-02

Note: I am already aware of how to return a single result from my Hire table using an outer apply, advice which I got in a different thread.

I hope my question has made sense and that someone can help. In terms of SQL queries, this is a first for me so any advice and guidance are appreciated.

Answer 1

Are you looking for lead function ? :

SELECT h.*
FROM (SELECT h.*,
             LEAD(HireCompany) OVER (PARTITION BY CarID ORDER BY Id) AS ForwardHire,
             LEAD(Onhire) OVER (PARTITION BY CarID ORDER BY Id) AS ForwardHireDate
      FROM Hire h
     ) h
WHERE ForwardHire IS NOT NULL AND ForwardHireDate IS NOT NULL;