Conditional Date Comparison and Assignment using Case expression in Oracle SQL

I am new to the site and SQL. I need some help with a case expression.

The requirement is as follows:

I have a table T1 with two date columns – eff and disc;

A second table T2 with 2 date columns – on_date & off_date;

I am trying to build a single case expression where i can compare the date columns btw both the tables and assign values based on certain conditions.

The Conditions are:

• If T1.eff and T2.disc dates are set to default , i.e. T1.eff=1/1/1970 and T2.disc=1970

  Then set set T1.eff=T2.on_date and T2.disc=T2.off_date

• If T1.eff >T2.on_date and T1.disc >T2.off_date

 Then set T1.disc=T2.Off_date.

• If T1.eff

 Then set T1.eff=T2.On_date.

• If T1.eff T2.off_date

 Then set T1.eff=T2.On_date and T1.disc=T2.Off_date

• If T1.eff >T2.on_date and T1.disc

  Then do not update eff, disc dates, insert as is.

I started writing a Case expression and i am stuck on how to build/write the block; where i need to compare both ‘eff’ and ‘disc’ dates as one condition and then assign the respective value to both ‘eff’ and ‘disc’ in a single case expression.

SELECT

CASE T1.EFF, T1.DISC

WHEN T1.EFF = TO_DATE('01/01/1970', 'MM/DD/YYYY') AND DISC = TO_DATE('01/01/1970', 'MM/DD/YYYY')

THEN T1.EFF = T2.ON_DATE AND T1.DISC = T2.OFF_DATE

WHEN T1.EFF > T2.ON_DATE AND T1.DISC > T2.OFF_DATE

THEN T1.EFF = T1.EFF AND T1.DISC = T2.OFF_DATE

WHEN T1.EFF < T2.OFF_DATE AND T1.DISC > T2.OFF_DATE

THEN T1.EFF = T2.ON_DATE AND T1.DISC = T2.OFF_DATE

WHEN T1.EFF > T2.ON_DATE AND T1.DISC < T2.OFF_DATE

THEN T1.EFF = T1.EFF AND T1.DISC - T1.DISC

END, 

T2.ON_DATE, T2.OFF_DATE

FROM T2, T1

WHERE T1.A = T2.B 

ETC.

I am not sure if we can get/use two columns in a single case expression.

Oracle DB – Client version: 12.1.0.2.0

Thanks in Advance! => VPPG

Answer

I think what you want is to modify the values for EFF and DISC that are displayed in the SELECT output. You can’t easily do that with a single case statement, since you want to change the output of 2 columns. But if you simplify your logic, you can still keep it pretty simple.

SELECT
    CASE WHEN T1.EFF = TO_DATE('01/01/1970', 'MM/DD/YYYY') AND DISC = TO_DATE('01/01/1970', 'MM/DD/YYYY')
        THEN T2.ON_DATE
        WHEN T1.EFF < T2.OFF_DATE AND T1.DISC > T2.OFF_DATE
        THEN T2.ON_DATE
        ELSE T1.EFF
    END as EFF, 
    CASE WHEN T1.EFF > T2.ON_DATE AND T1.DISC < T2.OFF_DATE
        THEN T1.DISC
        ELSE T2.OFF_DATE
    END as DISC,
    T2.ON_DATE, T2.OFF_DATE
FROM T2, T1
WHERE T1.A = T2.B;

Also note that I’m using a different form of the CASE expression – there are two ways to use it.

If this doesn’t really solve your problem, let me know and I’ll update my answer.