[Stackoverflow] [Progress OpenEdge ABL] SQL list all apartments with actual owner

[user12790006]

I'm trying to use a query to list all apartments with actual owner.

My apartment table look like this:

building | apartmentNbr | owner | start | end
b1 | a1 | o1 | 2009-08-13 | 2010-08-13
b1 | a1 | o2 | 2010-08-14 | 2019-01-01
b1 | a2 | o3 | 2009-01-03 | 2010-01-03
b1 | a2 | o4 | 2010-01-04 | 2010-08-13
b1 | a2 | o5 | 2010-08-14 | 9999-12-31
b1 | a3 | o6 | 2010-08-14 | 2019-12-31
b1 | a3 | o7 | 2020-02-14 | 9999-12-31
b2 | b1 | o8 | 2012-08-14 | 9999-12-31
...


With my query

SELECT *
FROM Appartments
WHERE CURDATE() BETWEEN startDate AND endDate


I only have the occupied apartments but I would like to have also free like this:

building | apartmentNbr | owner
b1 | a1 | null
b1 | a2 | o5
b1 | a3 | null
b2 | b1 | o8


How can I fix this?

Continue reading...