I am trying to compare dates to find when someone is no longer eligible. In this sample they are not eligible from 10/1/23 to 10/19/23. How do I find that range? Thanks.
enter image description here
CREATE TABLE ADMITS
(
ID_NUM INT
,AUTH_EFF_DATE date null
,AUTH_EXP_DATE date null
,MEM_EFF_DATE date null
,MEM_EXP_DATE date null
)
INSERT INTO ADMITS (ID_NUM,AUTH_EFF_DATE,AUTH_EXP_DATE,MEM_EFF_DATE,MEM_EXP_DATE)
VALUES
(118206307, '1/2/2023', '6/7/2023', '4/22/2022', '9/30/2023')
,(118206307, '8/30/2023', '2/17/2024', '4/22/2022', '9/30/2023')
,(118206307, '1/2/2023', '6/7/2023', '10/20/2023', '12/31/9999')
,(118206307, '8/30/2023', '2/17/2024', '10/20/2023', '12/31/9999')
;
From that I’d like to compare that date range to the auth_eff_date and auth_exp_date to show what auth ranges had missing eligibilities. I thought maybe lead or lag but wasn’t sure how or if that was right.
user24472185 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.