Hi,
I try to compare 2 list of dates, but not really found a "smart" way to do it...
[Table 1] I have a list of object with their created date (yyyy-mm)
| Object ID | Create Date |
| O1 | 2022-06 |
| O2 | 2022-06 |
O3 | 2022-07 |
[Table 2] And I have a list of used objects per month:
| Date Used | Object Id |
| 2022-06 | O1 |
| 2022-07 | O1 |
| 2022-08 | O1 |
| 2022-08 | O3 |
| 2022-09 | O1 |
| 2022-09 | O2 |
| 2022-09 | O3 |
[Table 3] And I have a list of date to compute the result for each month:
| Date |
| 2022-05 |
| 2022-06 |
| 2022-07 |
| 2022-08 |
| 2022-09 |
What I try to compute is the number of object non used per month.
In my example:
- 2022-05 : all object didn't exist yet --> not used = 0
- 2022-06 : O1 is used but not O2 ; O3 didn't exist yet (so not to take into the SUM for this month) --> not used = 1
- 2022-07: all objects exists, O1 is used, but not O2 and not O3 --> not used = 2
- 2022-08 : all objects exists, O1 and O3 are used but not O2 --> not used = 1
- 2022-09: all objects exists and used --> not used = 0
My first idea is to do a batch macro on each date of [Table 3] , example for 1 step : process date "2022-06".
This batch macro take the 2 other full tables [Table 1] and [Table 2] in input and process like this:
- [Table 1] Keep all objects with created date <= date in input of the macro.
- For input "2022-06" --> keep O1 and O2 (and we can count 2 existing objects)
- [Table 2] Keep all lines with date used == date in input of the macro
- For input "2022-06", keep only first line with [2022-06 | O1] --> count used objects = 1
- Finally number of not used object is "count existing object" - "count used objects" = 2 - 1 = 1
But is there a smarter way to do this without a batch macro?
Alex
