i have some issue trying to simplifying my code with the function Exists(). Let me first explain where my data come from with an example. I need to compare 2 date fields 1- Load Date with selected Document and ID with Load Date (all Dates without any selection)
this is my table, every Document has multiple IDs.
Test1:
load
Document, ID, Load Date
from QVD;
Table1:
Document
ID
Load Date
123
1
12/3/2021
123
1
10/4/2021
123
1
9/28/2021
456
1
6/23/2020
456
3
11/6/2021
456
3
1/5/2020
for example; When I selected the Document 123 and ID 1 I got this data:
Document
ID
Load Date
123
1
12/3/2021
123
1
10/4/2021
123
1
9/28/2021
Requirement:
in above table; Document and ID I have a 3 load Dates selected ( 12/3/2021, 10/4/2021, 9/28/2021) .
and I have a (load Dates) all dates desc list without any selection
Load Date
12/3/2021
11/6/2021
10/4/2021
9/28/2021
6/23/2020
1/5/2020
I need to create a check if the selected (Load Date) are exist in unselected (Load Date) list with continuation.( if previous date exist = 1 not = 0)
Load Date are exists in selected (Load Date) (in green) but not in continuation like 11/6/2021 in between the two dates .
the expected result will be when I selected the Document 123 and ID 1 :
Document
ID
Load Date
Expected field (Check)
123
1
12/3/2021
0 because the previous date not exist in this like in unselected (Load Date) list 11/6/2021
123
1
10/4/2021
1 because previous date exist in this and in unselected (Load Date ) list
123
1
9/28/2021
0
I have some idea to do this but not sure like create another table with key like (Document, ID and Date) and then put exist function but how to check the previous data / continuous date exist.
