INSERT INTO Datediff column based on conditions

Question

I have a table in Hive with columns:

COL_NAME
patient_num       int
proc_start_date   string
proc_end_date     string
lab               string
lab_start_date    string
lab_val           double
units             string

I want to add a Datediff column that returns days before in case of labs taken before the procedure (proc_start_date), and days after of labs taken after end of the procedure (proc_end_date).

INSERT INTO TABLE t2 SELECT t.*
datediff(lab_start_date, proc_start_date) WHERE lab_start_date < proc_start_date
datediff(lab_start_date, proc_end_date) WHERE lab_start_date > proc_start_date
FROM t2

Could you advise on editing my syntax or using different functions? Thanks in advance

Answer 1

You can use case when.

SELECT t.*,
Case when lab_start_date < proc_start_date then datediff(lab_start_date, proc_start_date) 
when lab_start_date > proc_start_date then 
datediff(lab_start_date, proc_end_date)
End dadediff_col
FROM t2 t