SQL code to create min date column based on multiple date columns-CodePudding

I have an SQL table with multiple date columns, some of which are blank (Example of Table) and I want to create a column in SQL that calculates the earliest date across the 3 columns (Expected Output)

Any help will be appreciated!

CodePudding user response：

Please always mention the db name with version for better solution.

Schema and insert statements:

 create table Patients (Patient varchar(50), Doctor_Date date, Nurse_Date date, Physio_Date date);

 insert into Patients (Patient,Doctor_Date)values ('Patient 1','26/11/2021');
 insert into Patients (Patient,Doctor_Date,Nurse_Date,Physio_Date) values('Patient 2','17/11/2021','20/11/2021','16/11/2021');
 insert into Patients (Patient,Nurse_Date,Physio_Date) values('Patient 3','5/12/2021','4/05/2021');

Query:

  select Patient, 
              (SELECT MIN(mindate) FROM (VALUES (Doctor_Date), (Nurse_Date), (Physio_Date)) AS X(mindate)) AS Minimum_Date
  from Patients

Output:

patient	minimum_date
Patient 1	2021-11-26
Patient 2	2021-11-16
Patient 3	2021-05-04

db<>fiddle here