I have requirement where table A is having the cont_number,start_dates and end_dates and table B is having cont_number,numberofdays(numbers of days having + and - values eg:30,-20) Table A:
| cont_number | start_date | end_date |
|---|---|---|
| 276820 | 01-Jul-2021 | 31-Jul-2021 |
| 817689 | 01-Jun-2021 | 30-Jun-2021 |
| 827628 | 01-Sep-2021 | 30-sep-2021 |
Table-B
| cont_number | Numberofdays |
|---|---|
| 276820 | -30 |
| 817689 | 40 |
| 827628 | 20 |
If Table B Numberofdays having +ve value it should add those days to end_date of Table-A and if it is -ve value then it should add to start_date Please help me with this below requirement
Expecting output:
| cont_number | start_date | end_date | new_start_date | new_end_date |
|---|---|---|---|---|
| 276820 | 01-Jul-2021 | 31-Jul-2021 | 01-Jun-2021 | 31-Jul-2021 |
| 817689 | 01-Jun-2021 | 30-Jun-2021 | 01-Jun-2021 | 09-Aug-2021 |
| 827628 | 01-Sep-2021 | 30-sep-2021 | 01-Sep-2021 | 20-Oct-2021 |
you can use if or case-when in impala.
select
a.cont_number cont_number,
a.start_date,
a.end_date,
if (b.Numberofdays>= 0 then a.end_date+ interval b.Numberofdays days,a.start_date) new_start_date,
if (b.Numberofdays< 0 then a.start_date+ interval b.Numberofdays days,a.end_date) new_end_date
from tablea A, tableb B
where a.cont_number=b.cont_number
new_start_date is calculated as if Numberofdays >=0 then end_date + Numberofdays else use original start date.
new_end_date is calculated as if Numberofdays <0 then start_date + Numberofdays else use original end date.