Forum Discussion
Helper IFind the missing numbers in the sequency across many dates and for many different sequences
Hi DaGemsta ,
I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting and desensitized example data.
Thanks for your efforts & time in advance.
Best regards,
Community Support Team_Binbin Yu
Helper IHi HotChilli
Sorry I didn't explain that very well.
Here's an example - EJ19 ABC had a firmware upgrade on the 25th Nov and that reset the ticket count back to 1.
| Meter Ticket No | Truck | Delivery Date |
| 415 | EJ19 ABC | 22-Nov-22 |
| 416 | EJ19 ABC | 22-Nov-22 |
| 417 | EJ19 ABC | 22-Nov-22 |
| 418 | EJ19 ABC | 22-Nov-22 |
| 419 | EJ19 ABC | 22-Nov-22 |
| 422 | EJ19 ABC | 22-Nov-22 |
| 423 | EJ19 ABC | 22-Nov-22 |
| 426 | EJ19 ABC | 23-Nov-22 |
| 427 | EJ19 ABC | 23-Nov-22 |
| 428 | EJ19 ABC | 23-Nov-22 |
| 429 | EJ19 ABC | 23-Nov-22 |
| 430 | EJ19 ABC | 23-Nov-22 |
| 433 | EJ19 ABC | 24-Nov-22 |
| 434 | EJ19 ABC | 24-Nov-22 |
| 435 | EJ19 ABC | 24-Nov-22 |
| 436 | EJ19 ABC | 24-Nov-22 |
| 437 | EJ19 ABC | 24-Nov-22 |
| 438 | EJ19 ABC | 24-Nov-22 |
| 439 | EJ19 ABC | 24-Nov-22 |
| 1 | EJ19 ABC | 25-Nov-22 |
| 2 | EJ19 ABC | 25-Nov-22 |
| 3 | EJ19 ABC | 25-Nov-22 |
| 4 | EJ19 ABC | 25-Nov-22 |
| 6 | EJ19 ABC | 25-Nov-22 |
| 10 | EJ19 ABC | 25-Nov-22 |
| 11 | EJ19 ABC | 25-Nov-22 |
| 12 | EJ19 ABC | 25-Nov-22 |
| 13 | EJ19 ABC | 25-Nov-22 |
| 15 | EJ19 ABC | 26-Nov-22 |
| 22 | EJ19 ABC | 26-Nov-22 |
| 23 | EJ19 ABC | 26-Nov-22 |
| 24 | EJ19 ABC | 26-Nov-22 |
| 25 | EJ19 ABC | 26-Nov-22 |
| 26 | EJ19 ABC | 26-Nov-22 |
| 27 | EJ19 ABC | 26-Nov-22 |
| 30 | EJ19 ABC | 26-Nov-22 |
| 31 | EJ19 ABC | 26-Nov-22 |
| 32 | EJ19 ABC | 26-Nov-22 |
| 33 | EJ19 ABC | 26-Nov-22 |
| 35 | EJ19 ABC | 27-Nov-22 |
| 37 | EJ19 ABC | 27-Nov-22 |
| 38 | EJ19 ABC | 27-Nov-22 |
| 39 | EJ19 ABC | 27-Nov-22 |
| 40 | EJ19 ABC | 27-Nov-22 |
| 41 | EJ19 ABC | 27-Nov-22 |
| 42 | EJ19 ABC | 28-Nov-22 |
| 43 | EJ19 ABC | 28-Nov-22 |
| 44 | EJ19 ABC | 28-Nov-22 |
| 45 | EJ19 ABC | 28-Nov-22 |
| 48 | EJ19 ABC | 28-Nov-22 |
| 49 | EJ19 ABC | 28-Nov-22 |
| 50 | EJ19 ABC | 28-Nov-22 |
| 51 | EJ19 ABC | 29-Nov-22 |
| 52 | EJ19 ABC | 29-Nov-22 |
| 55 | EJ19 ABC | 29-Nov-22 |
| 56 | EJ19 ABC | 29-Nov-22 |
| 57 | EJ19 ABC | 29-Nov-22 |
| 58 | EJ19 ABC | 29-Nov-22 |
| 59 | EJ19 ABC | 29-Nov-22 |
| 60 | EJ19 ABC | 30-Nov-22 |
| 61 | EJ19 ABC | 30-Nov-22 |
| 62 | EJ19 ABC | 30-Nov-22 |
| 63 | EJ19 ABC | 30-Nov-22 |
So this truck now has two sequences, which means 2 MIN's and 2 MAX's.
First sequence will have MIN = 415 and MAX = 439
Second sequence will have MIN = 1 and MAX = 63
With the current method, the MIN for this truck shows as 1 and MAX shows as 439, which is giving the wrong amount of missing numbers.
Is there a way, by using the date maybe, to identify how may sequences a truck has and record the 2 x MIN's and 2 x MAX's? So you could have MINA = 415 MAXA = 439 and MINB = 1 MAXB = 63 and then work out the missing tickets from the two?
I hope that makes more sense?
Hi DaGemsta ,
You could try to create a firmware upgrade date table for each truck, then according the date table to calculate.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.