Hi All,
I have two table contract and revenue and also mentioned the output I need.
Contract table has contract for equipment with valid start and end date. Revenue table has the details about when the equipment was repaired and charges against them.
| Input 1 : Contract Data | |
| Equipment | Contract | Start | End |
| 1 | C001 | 12/17/2018 | 12/17/2019 |
| 1 | C002 | 12/18/2019 | 12/17/2020 |
| 1 | C003 | 12/18/2020 | 12/18/2021 |
| 2 | C004 | 6/17/2018 | 6/17/2019 |
| 2 | C005 | 6/6/2020 | 6/6/2021 |
| 4 | C006 | 1/15/2018 | 1/15/2019 |
| Input 2 : Revenue Data | |
| Equipment | Posting Date | Revenue |
| 1 | 9/8/2018 | 284 |
| 1 | 7/5/2019 | 220 |
| 1 | 2/6/2020 | 203 |
| 1 | 3/28/2021 | 296 |
| 2 | 4/28/2018 | 209 |
| 2 | 7/2/2019 | 281 |
| 2 | 7/26/2020 | 283 |
| 3 | 9/8/2018 | 289 |
| 3 | 7/5/2019 | 298 |
| 4 | 11/26/2017 | 257 |
| 4 | 3/6/2018 | 221 |
| 4 | 3/6/2019 | 252 |
| Output : | | | | |
| Equipment | Posting Date | Revenue | Flag | Contract ID |
| 1 | 9/8/2018 | 284 | Not in Contract | |
| 1 | 7/5/2019 | 220 | In Contract | C001 |
| 1 | 2/6/2020 | 203 | In Contract | C002 |
| 1 | 3/28/2021 | 296 | In Contract | C003 |
| 2 | 4/28/2018 | 209 | Not in Contract | |
| 2 | 7/2/2019 | 281 | Not in Contract | |
| 2 | 7/26/2020 | 283 | In Contract | C005 |
| 3 | 9/8/2018 | 289 | Not in Contract | |
| 3 | 7/5/2019 | 298 | Not in Contract | |
| 4 | 11/26/2017 | 257 | Not in Contract | |
| 4 | 3/6/2018 | 221 | In Contract | C006 |
| 4 | 3/6/2019 | 252 | Not in Contract | |
The problem is to get details 'Not in Contract' where the Posting_date is not in the Start Date and End Date Range.
Kindly help to resolve this. Appreciate your help in advance.
