Can someone help me with the last step of my Query.
I have this table fiddle
CREATE TABLE rent(id integer,start_date date, end_date date,objekt_id integer,person_id integer);
INSERT INTO rent VALUES
(1, '2011-10-01','2015-10-31',5156,18268),
(2, '2015-11-01','2018-04-30',5156,18268),
(3, '2018-05-01','2021-03-31',5156,18269),
(4, '2021-04-01','2021-05-15',5156,null),
(5, '2021-05-16','2100-01-01',5156,18270),
(6, '2021-03-14','2021-05-15',5160,18270),
(7, '2021-05-16','2100-01-01',5160,18271);
With lag and lead i want two columns for last person_id and next person_id.
With this Query i almost solved my Problem but there is still one thing i need help to change.
with tbl as (
SELECT rent.*,
row_number() over (PARTITION BY objekt_id) as row_id
FROM rent
ORDER BY id)
SELECT r.id,
r.start_date,
r.end_date,
r.objekt_id,
r.person_id,
lag(person_id) over (PARTITION BY objekt_id, person_id IS NOT NULL AND objekt_id IS NOT NULL ORDER BY id) as last_person,
lead(person_id) over (PARTITION BY objekt_id, person_id IS NOT NULL AND objekt_id IS NOT NULL ORDER BY id) as next_person
FROM tbl r
order by 1;
Last or Next Person_id always have to either null or from another person_id.
At the moment row 2 will give me last_person_id = 18268 since row 1 had the same person_id. If person_id is empty i also want to see last and next person.
Output now:
id start_date end_date objekt_id person_id last_person next_person
1 2011-10-01 2015-10-31 5156 18268 18268
2 2015-11-01 2018-04-30 5156 18268 18268 18269
3 2018-05-01 2021-03-31 5156 18269 18268 18270
4 2021-04-01 2021-05-15 5156
5 2021-05-16 2100-01-01 5156 18270 18269
6 2021-03-14 2021-05-15 5160 18270 18271
7 2021-05-16 2100-01-01 5160 18271 18270
Wished Output:
id start_date end_date objekt_id person_id last_person next_person
1 2011-10-01 2015-10-31 5156 18268 18269
2 2015-11-01 2018-04-30 5156 18268 18269
3 2018-05-01 2021-03-31 5156 18269 18268 18270
4 2021-04-01 2021-05-15 5156 18269 18270
5 2021-05-16 2100-01-01 5156 18270 18269
6 2021-03-14 2021-05-15 5160 18270 18271
7 2021-05-16 2100-01-01 5160 18271 18270
The goal with query is to choose a specific date and to tell if the object is for rent or not and then also show who rent's it at and who was the last one and is there someone in line to rent
You can try to use correlated-subquery to make it by your logic condition.
with tbl as (
SELECT rent.*,
row_number() over (PARTITION BY objekt_id) as row_id
FROM rent
ORDER BY id)
SELECT r.id,
r.start_date,
r.end_date,
r.objekt_id,
r.person_id,
( SELECT t1.person_id
FROM tbl t1
WHERE t1.objekt_id = r.objekt_id
AND t1.id < r.id
AND (t1.person_id <> r.person_id OR r.person_id IS NULL)
AND t1.person_id IS NOT NULL
ORDER BY t1.id desc
LIMIT 1) last_person,
(SELECT t1.person_id
FROM tbl t1
WHERE t1.objekt_id = r.objekt_id
AND t1.id > r.id
AND (t1.person_id <> r.person_id OR r.person_id IS NULL)
AND t1.person_id IS NOT NULL
ORDER BY t1.id
LIMIT 1) next_person
FROM tbl r
order by 1;