I am trying to fix a sql to generate rows between 2 dates. In the below case a patient has come to hospital on 2023-09-01 and got status green but on 2023-09-03 got stauts yellow , so the date 2023-09-02 is green which i want to show.Thanks in advance

PatientId Department Date Status
1 A 2023-09-01 Green
1 A 2023-09-03 Yellow
1 B 2023-09-05 Red

Result

PatientId Department Date Status
1 A 2023-09-01 Green
1 A 2023-09-02 Green
1 A 2023-09-03 Yellow
1 A 2023-09-04 Yellow
1 B 2023-09-05 Red

I am trying to fix a sql to generate rows between 2 dates. In the below case a patient has come to hospital on 2023-09-01 and got status green but on 2023-09-03 got stauts yellow , so the date 2023-09-02 is green which i want to show.Thanks in advance

Result

PatientId Department Date Status
1 A 2023-09-01 Green
1 A 2023-09-02 Green
1 A 2023-09-03 Yellow
1 A 2023-09-04 Yellow
1 B 2023-09-05 Red