'Find next visit date when it is null

I'm trying to find the next visit date but whenever it is null it doesn't show the original date. In the sample below the 12/9/21 does not show. Thanks.

I'm not sure how to paste as a table?

table a

ID_NUM  START_DATE  END_DATE
454 2021-10-26  2021-10-29
454 2021-11-26  2021-12-02
454 2021-12-04  2021-12-09

    WITH QUARTERS AS
(
SELECT DISTINCT ID_NUM
,START_DATE
,END_DATE
FROM WASH_Q4
)


SELECT CLAIM.ID_NUM
,QUARTERS.END_DATE
,MIN(CLAIM.SVCDAT) AS MIN_SERVICE_DATE
FROM CLAIM
    left JOIN QUARTERS ON CLAIM.ID_NUM = QUARTERS.ID_NUM
WHERE CLAIM.SVCDAT > QUARTERS.END_DATE
GROUP BY CLAIM.ID_NUM
,QUARTERS.END_DATE

Results:

ID_NUM  END_DATE    MIN_SERVICE_DATE
454 2021-10-29  2021-11-27
454 2021-12-02  2021-12-05

Want:

ID_NUM  END_DATE    MIN_SERVICE_DATE
454 2021-10-29  2021-11-27
454 2021-12-02  2021-12-05
454 2021-12-09  NULL
sql-server


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions