Date Comparisons between tables using a dynamic variable for the "no of days between"
Hi all,
Wonder if you can help me.
I have 2 tables which both have an eventID and dates and i need to identify which dates in one table happen BEFORE the date in the other table. As an example:
in table 1, I have eventID and a date.
eg. Table 1
Event ID
Date
Event 1
4th Feb 2021
Event 2
20 Dec 2020
in table 2, i have a number of different rows involving event IDs and different dates,
Row ID
Event ID
Date
1
Event 1
1st Dec 2020
2
Event 2
12th Dec 2020
3
Event 1
27th Jan 2021
4
Event 1
1st Feb 2021
5
Event 1
1st Mar 2021
My requirement on my app is to find all the dates in table 2 which occur BEFORE the date with the same event ID in table 1.
On my app, i am going to add a slider attached to a variable (vNoOfDays) which will act as a filter which allows me to set an integer to dynamically define "how many days before".
so if my slider is set to 5, i want to include row 4 from table 2 as 1st Feb is < 5 days from my event date in table 1 (4th Feb) for event 1.
If the slider is set to 15, i want to include:
row 4 and row 3 as both 27th Jan 2021 and 1st feb 2021 are < 15 days from 4th Feb for Event 1
row 2 as 12th Dec is < 15 days from 20th Dec for Event 2
Hoping that makes sense!
Basically my date in table 1 is a hard date and i need to find all the rows in table 2 which fall before the hard date, but the number of days before is determined by my dynamic slider.
