Hi Everyone,
I have 2 tables,
Table 1 : Sim Data
| SOR_ID | DEVICE_START_DT | EQP_DEVICE_ID |
| V | 28-Apr-21 | 350039000000000 |
| V | 14-Apr-21 | 350039000000000 |
| V | 19-May-21 | 350039000000000 |
| V | 19-May-21 | 350039000000000 |
| V | 21-Feb-20 | 350039000000000 |
| V | 21-May-21 | 350039000000000 |
| V | 17-May-21 | 350039000000000 |
| V | 30-Mar-21 | 350039000000000 |
| V | 11-Oct-19 | 350039000000000 |
| V | 21-May-21 | 350039000000000 |
| V | 12-Jun-21 | 350039000000000 |
| V | 3-Jul-21 | 350039000000000 |
| V | 19-May-21 | 350039000000000 |
| V | 11-May-21 | 350039000000000 |
| V | 26-May-21 | 350039000000000 |
| V | 18-Mar-21 | 350039000000000 |
| V | 19-Jun-21 | 350039000000000 |
| V | 10-Apr-21 | 350131000000000 |
| V | 19-May-21 | 350131000000000 |
| V | 30-Jun-21 | 350131000000000 |
| V | 19-May-21 | 350131000000000 |
| V | 17-May-21 | 350131000000000 |
| V | 14-May-21 | 350131000000000 |
| V | 9-May-21 | 350131000000000 |
| V | 19-May-21 | 350131000000000 |
| V | 18-May-21 | 350131000000000 |
| V | 20-May-21 | 15588000000000 |
| V | 7-May-21 | 15588000000000 |
| V | 10-Apr-21 | 15588000000000 |
| V | 6-May-21 | 15588000000000 |
| V | 19-May-21 | 15588000000000 |
| V | 18-May-21 | 15588000000000 |
| V | 30-Jun-21 | 15588000000000 |
Table 2 : Activation
| SOR_ID | EFF_DT | EXP_DT | EQP_DEVICE_ID |
| V | 29-Mar-21 | 13-Apr-21 | 15588000000000 |
| V | 1-Apr-21 | 16-Apr-21 | 15588000000000 |
| V | 13-May-21 | 28-May-21 | 15588000000000 |
| V | 5-May-21 | 20-May-21 | 15588000000000 |
| V | 11-May-21 | 26-May-21 | 15588000000000 |
| V | 9-May-21 | 24-May-21 | 350039000000000 |
| V | 11-May-21 | 26-May-21 | 350039000000000 |
| V | 11-May-21 | 26-May-21 | 350039000000000 |
| V | 16-May-21 | 31-May-21 | 350039000000000 |
| V | 17-May-21 | 1-Jun-21 | 350039000000000 |
| V | 17-May-21 | 1-Jun-21 | 350039000000000 |
| V | 18-May-21 | 2-Jun-21 | 350039000000000 |
| V | 18-May-21 | 2-Jun-21 | 350039000000000 |
| V | 20-May-21 | 4-Jun-21 | 350039000000000 |
| V | 21-May-21 | 5-Jun-21 | 350039000000000 |
| V | 21-May-21 | 5-Jun-21 | 350039000000000 |
| V | 21-May-21 | 5-Jun-21 | 350039000000000 |
| V | 7-Jun-21 | 22-Jun-21 | 350039000000000 |
| V | 10-Jun-21 | 25-Jun-21 | 350039000000000 |
| V | 30-Jun-21 | 15-Jul-21 | 350039000000000 |
| V | 10-Jun-21 | 25-Jun-21 | 350131000000000 |
| V | 30-May-21 | 14-Jun-21 | 350131000000000 |
| V | 21-Jun-21 | 6-Jul-21 | 350131000000000 |
| V | 10-May-21 | 25-May-21 | 350131000000000 |
In the above mentioned tables, we need to get the interval dates of Device_Start_Date based on EFF_Dt & Exp_Dt
By using the code given below,
SIM_DATA:
LOAD
DEVICE_START_DT,
EQP_DEVICE_ID as DEVICE_ID
FROM [lib://AttachedFiles/SIM_DEVICE_DATA.xlsx]
(ooxml, embedded labels, table is SIM_DEVICE_DATA);
Activation:
LOAD
EFF_DT,
EXP_DT,
MTN,
EQP_DEVICE_ID as DEVICE_ID
FROM [lib://AttachedFiles/SIM_DEVICE_DATA.xlsx]
(ooxml, embedded labels, table is ACTIVATIONS_DEVICE_DATA);
tmp:
inner Join (Activation)
IntervalMatch(DEVICE_START_DT, DEVICE_ID)
load EFF_DT, EXP_DT, DEVICE_ID
Resident Activation;
got the output as given below which is correct.
| DEVICE_ID | EFF_DT | EXP_DT | Interval Dt |
| 350039000000000 | 09-May-2021 | 24-May-2021 | 11-May-2021 |
| 350039000000000 | 09-May-2021 | 24-May-2021 | 17-May-2021 |
| 350039000000000 | 09-May-2021 | 24-May-2021 | 19-May-2021 |
| 350039000000000 | 09-May-2021 | 24-May-2021 | 21-May-2021 |
| 350039000000000 | 11-May-2021 | 26-May-2021 | 17-May-2021 |
| 350039000000000 | 11-May-2021 | 26-May-2021 | 19-May-2021 |
| 350039000000000 | 11-May-2021 | 26-May-2021 | 21-May-2021 |
| 350039000000000 | 11-May-2021 | 26-May-2021 | 26-May-2021 |
| 350039000000000 | 16-May-2021 | 31-May-2021 | 17-May-2021 |
| 350039000000000 | 16-May-2021 | 31-May-2021 | 19-May-2021 |
| 350039000000000 | 16-May-2021 | 31-May-2021 | 21-May-2021 |
| 350039000000000 | 16-May-2021 | 31-May-2021 | 26-May-2021 |
| 350039000000000 | 17-May-2021 | 01-Jun-2021 | 19-May-2021 |
| 350039000000000 | 17-May-2021 | 01-Jun-2021 | 21-May-2021 |
| 350039000000000 | 17-May-2021 | 01-Jun-2021 | 26-May-2021 |
| 350039000000000 | 18-May-2021 | 02-Jun-2021 | 19-May-2021 |
| 350039000000000 | 18-May-2021 | 02-Jun-2021 | 21-May-2021 |
| 350039000000000 | 18-May-2021 | 02-Jun-2021 | 26-May-2021 |
| 350039000000000 | 20-May-2021 | 04-Jun-2021 | 21-May-2021 |
| 350039000000000 | 20-May-2021 | 04-Jun-2021 | 26-May-2021 |
| 350039000000000 | 21-May-2021 | 05-Jun-2021 | 26-May-2021 |
| 350039000000000 | 07-Jun-2021 | 22-Jun-2021 | 12-Jun-2021 |
| 350039000000000 | 07-Jun-2021 | 22-Jun-2021 | 19-Jun-2021 |
| 350039000000000 | 10-Jun-2021 | 25-Jun-2021 | 12-Jun-2021 |
| 350039000000000 | 10-Jun-2021 | 25-Jun-2021 | 19-Jun-2021 |
| 350039000000000 | 30-Jun-2021 | 15-Jul-2021 | 03-Jul-2021 |
Now my requirement is to get the Min Date & Max Date from the Interval Date in the above table.
How can I achieve it.
Please suggest.
Thanks in Advance.
