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.
