CODEWITHSUNDEEP
sqlsql-server
Yanayaya
Yanayaya

Reputation: 2184

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

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.

Upvotes: 2

Views: 167

Answers (4)

forpas
forpas

Reputation: 164089

Self join the hire table to the row that has the next onhire date:

select 
  h1.*,
  h2.hirecompany ForwardHire 
  h2.onhire ForwardHireDate
from hire h1 left join hire h2
on 
  (h2.carid = h1.carid) 
  and 
  (h2.onhire = (select min(onhire) from hire where carid = h1.carid and onhire > h1.offhire) )
where 
  h1.carid = 272
  and
  curdate() between h1.onhire and h1.offhire

Upvotes: 1

EzLo
EzLo

Reputation: 14189

Using OUTER APPLY:

SELECT
    H.*,
    T.ForwardHire,
    T.ForwardHireDate
FROM
    Hire AS H
    OUTER APPLY (
        SELECT TOP 1                        -- Just the next record
            ForwardHire = F.HireCompany,
            ForwardHireDate = F.OnHire
        FROM
            Hire AS F
        WHERE
            H.CarId = F.CarId AND           -- With the same car
            F.OnHire > H.OffHire            -- With later OnHire
        ORDER BY
            F.OnHire ASC                    -- Sorted by OnHire (closeste one first)
        ) AS T

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269683

Do you just want lead()?

select h.*,
       lead(h.hirecompany) over (partition by h.carid order by h.onhire) as next_hirecompany
from hire h;

Note: this will return the next company, even if there are gaps. If you want the "adjacent" next company, then I'd recommend a left join:

select h.*, hnext.hirecompany as next_hirecompany
from hire h left join
     hire hnext
     on hnext.carid = h.carid and
        hnext.onhire = dateadd(day, 1, h.offhire);

Upvotes: 2

Related Questions