Conditional Date Comparison and Assignment using Case expression in Oracle SQL

Question

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

What are your expected results from this SELECT statement? You can't assign values to columns within a SELECT. Do you want to UPDATE the values in the table, or just display the modified `eff` and `disc` values? — kfinity, Nov 27 '18 at 14:44
Hi Kfinity, Thanks for responding. Yes, i would like to fetch the updated/assigned values and insert into a table (t3) at a later time. — VPPG, Nov 27 '18 at 14:59

score 0 · Accepted Answer · answered Nov 27 '18 at 15:17

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.

Thank you, Kfinity. I will run this code and validate the results and will update you tomorrow. Thanks again! — VPPG, Nov 27 '18 at 16:05
The above code works for me. Thank you. Now, i have been asked to include a rejection logic for the same date validation. I am listing down the rejection logic below: Reject any records if T1.eff < T2. On_date and T1.disc < T2.On_date | • Reject any records if T1.eff > T2. Off_date and T1.disc > T2.Off_date • All the rejected rows are inserted into Reject_exception table (new, to be created). — VPPG, Nov 28 '18 at 10:10

Conditional Date Comparison and Assignment using Case expression in Oracle SQL

1 Answers1