End date with null value :db2-sql

Question

i have a table my_date

id        Start          END
1       2021-01-13     2021-04-15
1       2021-04-16     2021-11-28
1       2021-11-29        null
2       2021-05-05     2021-09-13
2       2021-09-13     2021-12-31
3       2020-01-09     2021-08-29
3       2021-08-30     2023-04-15

what i want to want to choose the id that have max(end)<= 2021-12-31 so my result should be just

id        Start          END
2       2021-09-13     2021-12-31

According to the description your result is wrong. It should be the next row `2 - 2021-09-13 - 2021-12-31`. — The Impaler, Nov 16 '21 at 14:27
max (end_dt) = 2021-11-28 for id = 1 is less than 2021-12-31 as well. Why don't we see the corresponding row in the output? — Mark Barinstein, Nov 16 '21 at 16:15
1 2021-04-16 2021-11-28 i donnot want because id 1 has another record 1 2021-11-29 null that null is consider oppen date — Tick _Tack, Nov 16 '21 at 17:15
Does this answer your question? [Fetch the row which has the Max value for a column](https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) — Shmiel, Nov 16 '21 at 18:19

score 0 · Answer 1 · answered Nov 16 '21 at 14:50

You can filter by the date limit, then order by date in descending manner, and finally retrieve the first row only.

For example:

select *
from t
where end <= date '2021-12-31'
order by end desc
fetch next 1 rows only

Result:

ID  START       END
--  ----------  ----------
 2  2021-09-13  2021-12-31

See running example at db<>fiddle.

score 0 · Answer 2 · answered Nov 16 '21 at 20:46

Try this:

SELECT id, Start, END
FROM
(
SELECT *
, ROW_NUMBER () OVER (PARTITION BY ID ORDER BY Start DESC) AS RN_
FROM
(
VALUES
  (1, '2021-01-13', '2021-04-15')
, (1, '2021-04-16', '2021-11-28')
, (1, '2021-11-29', null)
, (2, '2021-05-05', '2021-09-13')
, (2, '2021-09-13', '2021-12-31')
, (3, '2020-01-09', '2021-08-29')
, (3, '2021-08-30', '2023-04-15')
) T (id, Start, END)
) T
WHERE RN_ = 1 AND END <= '2021-12-31'

dbfiddle link

End date with null value :db2-sql

2 Answers2